{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7577159b",
   "metadata": {},
   "source": [
    "## SQLAlchemy 简介"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97eb376f",
   "metadata": {},
   "source": [
    "SQLAlchemy是一个 ORM 框架\n",
    "\n",
    "对象关系映射（英语：Object Relational Mapping，简称ORM\n",
    "\n",
    "用于实现面向对象编程语言里不同类型系统的数据之间的转换。\n",
    "\n",
    "大白话：对象模型 与 数据库表 的映射"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60f0e09d",
   "metadata": {},
   "source": [
    "**为什么要有 SQLAlchemy？**\n",
    "\n",
    "随着项目的越来越大，采用写原生SQL的方式在代码中会出现大量重复的SQL语句，那么，问题就出现了：\n",
    "1. SQL语句 重复利用率不高，越复杂的SQL语句条件越多，代码越长，会出现很多相近的SQL语句\n",
    "2. 如果数据库需要更改，就要去修改这些逻辑，这会容易漏掉对某些SQL语句的修改\n",
    "3. 写SQL时容易忽略web安全问题，造成隐患"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc37b3f9",
   "metadata": {},
   "source": [
    "而 ORM 可以通过 类的方式 去操作数据库而不用再写原生的SQL语句，通过把表映射成类，把行作为实例(一条数据)，把字段作为属性\n",
    "\n",
    "ORM 在执行对象操作的时候最终还是会把对象的操作转换为数据库的原生语句"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75fe1a07",
   "metadata": {},
   "source": [
    "```python\n",
    "class Person:\n",
    "    name = 'xx'\n",
    "    age = 18\n",
    "    country ='xx'\n",
    "   \n",
    "# Person类 -> 数据库中的一张表\n",
    "# Person类中的属性 -> 数据库中一张表字段\n",
    "# Person类的一个对象 -> 数据库中表的一条数据\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e23ef26",
   "metadata": {},
   "source": [
    "![](06_SQLAlchemy_images/1.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3060890",
   "metadata": {},
   "source": [
    "## 操作 数据库"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbe65e41",
   "metadata": {},
   "source": [
    "**首先，我们要开启数据库** "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b07ba3bb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-16T10:26:29.460962Z",
     "start_time": "2023-12-16T10:26:29.426577Z"
    }
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "def conn_db1():\n",
    "    \"\"\"连接数据库\"\"\"\n",
    "    \n",
    "    # 数据库的变量\n",
    "    HOST = '10.196.137.90'  # IP，也可写：127.0.0.1 / localhost\n",
    "    HOST = '127.0.0.1'\n",
    "    PORT = 3306 # 端口号\n",
    "    DATA_BASE = 'flask_db' # 数据库名\n",
    "    USER = 'root' # 用户名\n",
    "    PWD = 'zxydsg123' # 密码\n",
    "\n",
    "    # 设置数据库链接\n",
    "    # DB_URI = f'数据库的名+驱动名://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "    DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "    # 创建 链接引擎\n",
    "    engine = create_engine(DB_URI)\n",
    "    \n",
    "    ## 执行一个SQL\n",
    "    # 定义 sql语句\n",
    "    sql = 'select 2;' \n",
    "    \n",
    "    # 利用链接引擎，生成一个链接\n",
    "    conn = engine.connect() \n",
    "    \n",
    "    # 执行sql\n",
    "    rs = conn.execute(sql) \n",
    "    \n",
    "    # 打印结果\n",
    "    print(rs.fetchone())\n",
    "\n",
    "\n",
    "def conn_db2():\n",
    "    \"\"\"链接数据库\"\"\"\n",
    "    \n",
    "     # 数据库的变量\n",
    "    HOST = 'localhost'  # 127.0.0.1 / localhost\n",
    "    PORT = 3306\n",
    "    DATA_BASE = 'flask_db'\n",
    "    USER = 'root'\n",
    "    PWD = 'zxydsg123'\n",
    "\n",
    "    # DB_URI = f'数据库的名+驱动名://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "    DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "    # 创建一个引擎，专门链接数据库用的\n",
    "    engine = create_engine(DB_URI)\n",
    "    \n",
    "    # 定义 sql语句\n",
    "    sql = 'create table t_user1(id int primary key auto_increment, name varchar(32));'\n",
    "    \n",
    "    # 链接数据库\n",
    "    with engine.connect() as conn:\n",
    "        \n",
    "        # 执行 SQL\n",
    "        conn.execute(sql)\n",
    "\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    conn_db2()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84a01997",
   "metadata": {},
   "source": [
    "## 对象映射表"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01ea494f",
   "metadata": {},
   "source": [
    "![](06_SQLAlchemy_images/2.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9e67cfb",
   "metadata": {},
   "source": [
    "用 declarative_base 根据 engine 创建一个 ORM基类"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5de9444",
   "metadata": {},
   "source": [
    "```python\n",
    "# 导包\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "# 创建 引擎，用于链接数据库\n",
    "engine = create_engine(DB_URI)\n",
    "\n",
    "# 创建 ORM基类 -- 用于实现 对象到表的映射\n",
    "Base = declarative_base(engine)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c6603c8",
   "metadata": {},
   "source": [
    "用这个 Base 类作为基类来写自己的 ORM类。要定义 __tablename__ 类属性，来指定这个模型映射到数据库中的表名"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a2b7414",
   "metadata": {},
   "source": [
    "```python\n",
    "class Person(Base):\n",
    "    __tablename__ = 't_person'\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "888713f8",
   "metadata": {},
   "source": [
    "创建 属性 来映射到表中的字段，所有需要映射到表中的属性都应该为 Column类型"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24059384",
   "metadata": {},
   "source": [
    "```python\n",
    "class Person(Base):\n",
    "    __tablename__ = 't_person'\n",
    "    \n",
    "    # 在这个 ORM模型 中创建一些属性，来跟表中的字段进行一一映射\n",
    "    # 这些属性必须是sqlalchemy给我们提供好的数据类型\n",
    "    id = Column(Integer, \n",
    "                primary_key=True, # 主键\n",
    "                autoincrement=True) # 自增长\n",
    "    \n",
    "    name = Column(String(50)) \n",
    "    \n",
    "    age = Column(Integer)\n",
    "    \n",
    "    country = Column(String(50))\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7e1b7db",
   "metadata": {},
   "source": [
    "使用 Base.metadata.create_all() 来将模型映射到数据库中\n",
    "```python\n",
    "Base.metadata.create_all()\n",
    "```\n",
    "注意:\n",
    "- 一旦使用 Base.metadata.create_all() 将模型映射到数据库中后，即使改变了模型的字段，也不会再对表进行重新映射了"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e320fcb4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-16T11:18:26.054322Z",
     "start_time": "2023-12-16T11:18:25.969578Z"
    }
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine, Column, Integer, String\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "# 配置数据库的变量\n",
    "HOST = '127.0.0.1'\n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "\n",
    "# 配置数据库的链接\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "# 创建 链接引擎\n",
    "engine = create_engine(DB_URI)\n",
    "\n",
    "# 创建一个基类\n",
    "Base = declarative_base(engine)\n",
    "\n",
    "\n",
    "class Person(Base):\n",
    "    \n",
    "    # 定义表名\n",
    "    __tablename__ = 't_person'\n",
    "    \n",
    "    # 定义表中字段\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    name = Column(String(32))\n",
    "    age = Column(Integer)\n",
    "    country = Column(String(32))\n",
    "\n",
    "\n",
    "# 映射表结构\n",
    "Base.metadata.create_all()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3deeef50",
   "metadata": {},
   "source": [
    "## 数据的CRUD"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72aad52f",
   "metadata": {},
   "source": [
    "![](06_SQLAlchemy_images/3.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "260d74b3",
   "metadata": {},
   "source": [
    "### 构建Session对象"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73d7998a",
   "metadata": {},
   "source": [
    "所有和数据库的ORM操作都必须通过一个叫做 session 的会话对象来实现，通过以下代码来获取会话对象：\n",
    "```python\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "\n",
    "# 创建 与数据库的链接引擎\n",
    "engine = create_engine(DB_URI)\n",
    "\n",
    "# 创建 对象映射表 的基类\n",
    "Base = declarative_base(engine)\n",
    "\n",
    "# 创建 Session 类，实现对数据的 增删改查\n",
    "session = sessionmaker(engine)()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e379671f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-16T11:24:22.122230Z",
     "start_time": "2023-12-16T11:24:22.099197Z"
    }
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine,Column,Integer,String\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "\n",
    "# 配置 数据库的变量\n",
    "HOST = '127.0.0.1'  \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "# 创建 与数据库的链接引擎\n",
    "engine = create_engine(DB_URI)\n",
    "\n",
    "# 创建 对象映射表 的基类\n",
    "Base = declarative_base(engine)\n",
    "\n",
    "class Person(Base):\n",
    "    # 映射的 表名\n",
    "    __tablename__ = 't_person'\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    name = Column(String(32))\n",
    "    age = Column(Integer)\n",
    "    country = Column(String(32))\n",
    "\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "\n",
    "# 创建 Session 类，实现对数据的 增删改查\n",
    "Session = sessionmaker(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02200d35",
   "metadata": {},
   "source": [
    "### 添加对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f5a1d443",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-16T11:28:48.067849Z",
     "start_time": "2023-12-16T11:28:48.011561Z"
    }
   },
   "outputs": [],
   "source": [
    "def create_data_one():\n",
    "    \"\"\"添加一个数据\"\"\"\n",
    "    \n",
    "    # 创建 Session 实例\n",
    "    with Session() as session:\n",
    "        \n",
    "        # 创建 对象\n",
    "        p1 = Person(name='百战', age=6, country='北京')\n",
    "        \n",
    "        # 将对象 加载进 session\n",
    "        session.add(p1)\n",
    "        \n",
    "        # 提交事务\n",
    "        session.commit()\n",
    "\n",
    "def create_data_many():\n",
    "    \"\"\"添加多条数据\"\"\"\n",
    "    \n",
    "    # 创建 Session 实例\n",
    "    with Session() as session:\n",
    "        \n",
    "        # 创建 对象\n",
    "        p2 = Person(name='吕布', age = 19 , country='北京')\n",
    "        p3 = Person(name='貂蝉', age = 18 , country='北京')\n",
    "        \n",
    "        # 将多个对象 加载进 session\n",
    "        session.add_all([p2, p3])\n",
    "        \n",
    "        # 提交事务\n",
    "        session.commit()\n",
    "\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    create_data_one()\n",
    "    create_data_many()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73545623",
   "metadata": {},
   "source": [
    "### 查询对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "957e426a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-16T11:33:53.007374Z",
     "start_time": "2023-12-16T11:33:52.979095Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "百战\n",
      "吕布\n",
      "貂蝉\n",
      "百战\n",
      "19\n"
     ]
    }
   ],
   "source": [
    "def query_data_all():\n",
    "    \"\"\"查询所有数据\"\"\"\n",
    "    \n",
    "    # 创建 Session 实例\n",
    "    with Session() as session:\n",
    "        \n",
    "        # 查询所有人\n",
    "        all_person = session.query(Person).all()\n",
    "        \n",
    "        # 打印信息\n",
    "        for p in all_person:\n",
    "            print(p.name)\n",
    "\n",
    "def query_data_one():\n",
    "    \"\"\"查询单条数据\"\"\"\n",
    "    \n",
    "    # 创建 Session 实例\n",
    "    with Session() as session:\n",
    "        \n",
    "        # 查询首条数据\n",
    "        p1 = session.query(Person).first()\n",
    "        \n",
    "        # 打印信息\n",
    "        print(p1.name)\n",
    "\n",
    "def query_data_by_params():\n",
    "    \"\"\"通过参数，查询数据\"\"\"\n",
    "    \n",
    "    # 创建 Session 实例\n",
    "    with Session() as session:\n",
    "        \n",
    "        # 查询首条参数满足要求的数据\n",
    "        # p1 = session.query(Person).filter_by(name='吕布').first()\n",
    "        p1 = session.query(Person).filter(Person.name == '吕布').first()\n",
    "        print(p1.age)\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    query_data_all()\n",
    "    query_data_one()\n",
    "    query_data_by_params()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b99604b1",
   "metadata": {},
   "source": [
    "### 修改对象"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "41666fb1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-16T11:37:03.782341Z",
     "start_time": "2023-12-16T11:37:03.757976Z"
    }
   },
   "outputs": [],
   "source": [
    "def update_data():\n",
    "    \"\"\"更新数据\"\"\"\n",
    "    \n",
    "    # 创建 Session 实例\n",
    "    with Session() as session:\n",
    "        \n",
    "        # 找到 需要修改的 对象\n",
    "        p1 = session.query(Person).filter(Person.name == '吕布').first()\n",
    "        \n",
    "        # 进行修改\n",
    "        p1.age = 20\n",
    "        \n",
    "        # 提交事务\n",
    "        session.commit()\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    update_data()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41fe06aa",
   "metadata": {},
   "source": [
    "### 删除对象\n",
    "将需要删除的数据从数据库中查找出来，然后使用 session.delete 方法将这条数据从 session中删除，最后做commit操作就可以了"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "13e5a185",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-16T11:38:01.939906Z",
     "start_time": "2023-12-16T11:38:01.893443Z"
    }
   },
   "outputs": [],
   "source": [
    "def delete_data():\n",
    "    \"\"\"删除数据\"\"\"\n",
    "    \n",
    "    # 创建 Session 实例\n",
    "    with Session() as session:\n",
    "        \n",
    "        # 找到 需要删除的数据\n",
    "        p1 = session.query(Person).filter(Person.name == '貂蝉').first()\n",
    "        \n",
    "        # 删除数据\n",
    "        session.delete(p1)\n",
    "        \n",
    "        # 提交事务\n",
    "        session.commit()\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    delete_data()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36c8abea",
   "metadata": {},
   "source": [
    "## 常用数据类型"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e675faa2",
   "metadata": {},
   "source": [
    "**Integer：** 整形，映射到数据库中是int类型"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3534cf0d",
   "metadata": {},
   "source": [
    "**Float：** 浮点类型，映射到数据库中是float类型"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5307f107",
   "metadata": {},
   "source": [
    "**Double：** 双精度浮点类型，映射到数据库中是double类型，占据64位"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "424a1c8a",
   "metadata": {},
   "source": [
    "**String：** 可变字符类型，映射到数据库中是varchar类型"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f3ee0a1",
   "metadata": {},
   "source": [
    "**Boolean：** 布尔类型，映射到数据库中的是tinyint类型"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c9fc9a9",
   "metadata": {},
   "source": [
    "**DECIMAL：** 定点类型。\n",
    "- 是专门为了解决 浮点类型精度丢失的问题的，在存储钱相关的字段的时候建议大家都使用这个数据类型。\n",
    "- 这个类型使用的时候需要传递 两个参数：\n",
    "    1. 第一个参数是用来标记这个字段总能能存储多少个数字\n",
    "    2. 第二个参数表示小数点后有多少位。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64f3cd33",
   "metadata": {},
   "source": [
    "**Enum：** 枚举类型。\n",
    "- 指定某个字段只能是枚举中指定的几个值，不能为其他值。在ORM模型中，使用Enum来作为枚举，示例代码如下：\n",
    "```python\n",
    "class News(Base):\n",
    "    __tablename__ = 't_news'\n",
    "    tag = Column(Enum(\"python\", 'flask', 'django'))\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6996e461",
   "metadata": {},
   "source": [
    "- 在Python3中，已经内置了 enum这个枚举的模块，我们也可以使用这个模块去定义相关的字段。示例代码如下:\n",
    "\n",
    "```python\n",
    "class TagEnum(enum.Enum):\n",
    "    python = \"python\"\n",
    "    flask = \"flask\"\n",
    "    django = \"django\"\n",
    "\n",
    "class News(Base):\n",
    "    __tablename__ = 't_news'\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    tag = Column(Enum(TagEnum))\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da2c1d2c",
   "metadata": {},
   "source": [
    "**Date：** 存储时间，只能存储年月日。映射到数据库中是date类型。在Python代码中，可以使用 datetime.date 来指定。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "064fff16",
   "metadata": {},
   "source": [
    "**DateTime：** 存储时间，可以存储年月日时分秒毫秒等。映射到数据库中也是datetime类型。在Python代码中，可以使用 datetime.datetime 来指定。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ada900f",
   "metadata": {},
   "source": [
    "**Time：** 存储时间，可以存储时分秒。映射到数据库中也是 time类型。在Python代码中，可以使用 datetime.time，示例代码如下：\n",
    "```python\n",
    "class News(Base):\n",
    "    __tablename__ = 't_news'\n",
    "    create_time = Column(Time)\n",
    "\n",
    "news = News(create_time=time(hour=11, minute=11, second=11))\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f23e7ee6",
   "metadata": {},
   "source": [
    "**Text：** 存储长字符串。一般可以存储6W多个字符。如果超出了这个范围，可以使用LONGTEXT类型。映射到数据库中就是text类型\n",
    "\n",
    "**LONGTEXT：** 长文本类型，映射到数据库中是longtext类型。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d7cd844d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-16T12:23:43.090083Z",
     "start_time": "2023-12-16T12:23:42.960460Z"
    }
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "from sqlalchemy import Column, Integer, Float, DECIMAL, String, Boolean, Enum, Date, DateTime, Time, Text\n",
    "import enum\n",
    "from datetime import date, datetime, time\n",
    "\n",
    "# 配置数据库的变量\n",
    "HOST = '127.0.0.1'\n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "\n",
    "# 配置数据库链接\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "# 创建 链接引擎\n",
    "engine = create_engine(DB_URI)\n",
    "\n",
    "# 创建 对象映射表 基类\n",
    "Base = declarative_base(engine)\n",
    "\n",
    "# 创建 Session \n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "class TagEnum(enum.Enum):\n",
    "    \"\"\"枚举类型\"\"\"\n",
    "    python = 'Python'\n",
    "    flask = 'Flask'\n",
    "    mysql = 'MySQL'\n",
    "\n",
    "class News(Base):\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_news'\n",
    "    \n",
    "    # Integer 类型\n",
    "    id = Column(Integer, primary_key = True, autoincrement=True)\n",
    "    \n",
    "    # Float 类型\n",
    "    price1 = Column(Float) # 精度丢失\n",
    "    \n",
    "    # DECIMAL 类型\n",
    "    price2 = Column(DECIMAL)\n",
    "    \n",
    "    # String 类型\n",
    "    title = Column(String(32))\n",
    "    \n",
    "    # Boolean 类型\n",
    "    is_delelet = Column(Boolean)\n",
    "    \n",
    "    # 枚举类型\n",
    "    tag1 = Column(Enum('Python', 'Flask', 'MySQL'))\n",
    "    tag2 = Column(Enum(TagEnum))\n",
    "    \n",
    "    # Date 类型\n",
    "    create_time = Column(Date)\n",
    "    \n",
    "    # DateTime 类型\n",
    "    update_time = Column(DateTime)\n",
    "    \n",
    "    # Time 类型\n",
    "    delete_time = Column(Time)\n",
    "    \n",
    "    # Text 类型\n",
    "    content = Column(Text)\n",
    "\n",
    "def add_data():\n",
    "    \"\"\"添加数据\"\"\"\n",
    "    \n",
    "    # 创建对象\n",
    "    news1 = News(\n",
    "        price1=1000.00078,\n",
    "        price2=1000.00078,\n",
    "        title='测试SQLAlchemy数据',\n",
    "        is_delelet=True,\n",
    "        tag1='Python',\n",
    "        tag2='mysql',\n",
    "        create_time = date(2050, 11, 11),\n",
    "        update_time = datetime(2050, 11, 11,10,11,12),\n",
    "        delete_time = time(6,7,8),\n",
    "        content = 'Hello，我在测试数据'\n",
    "        )\n",
    "    \n",
    "    # 创建 Session 实例\n",
    "    with Session() as session:\n",
    "        \n",
    "        # 添加数据\n",
    "        session.add(news1)\n",
    "        \n",
    "        # 提交事务\n",
    "        session.commit()\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    # 映射表结构\n",
    "    Base.metadata.create_all()\n",
    "    \n",
    "    # 添加数据\n",
    "    add_data()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9b95ba9d",
   "metadata": {},
   "source": [
    "## Column 常用参数"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84e05a7e",
   "metadata": {},
   "source": [
    "![](06_SQLAlchemy_images/4.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72c19826",
   "metadata": {},
   "source": [
    "**primary_key：** True设置某个字段为主键\n",
    "\n",
    "**autoincrement：** True设置这个字段为自动增长的\n",
    "\n",
    "**default：** 设置某个字段的默认值，在发表时间这些字段上面经常用\n",
    "\n",
    "**nullable：** 指定某个字段是否为空，默认值是True，就是可以为空\n",
    "\n",
    "**unique：** 指定某个字段的值是否唯一，默认是False\n",
    "\n",
    "**onupdate：** 在数据更新的时候会调用这个参数指定的值或者函数。\n",
    "- 在第一次插入这条数据的时候，不会用onupdate的值，只会使用default的值。常用于是 update_time 字段（每次更新数据的时候都要更新该字段值）\n",
    "\n",
    "**name：** 指定ORM模型中某个属性映射到表中的字段名。\n",
    "- 如果不指定，那么会使用这个属性的名字来作为字段名。如果指定了，就会使用指定的这个值作为表字段名。\n",
    "- 这个参数也可以当作位置参数，在第1个参数来指定。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f2297c34",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-16T12:38:19.202115Z",
     "start_time": "2023-12-16T12:38:19.089519Z"
    }
   },
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "from sqlalchemy import Column, Integer, DateTime, String\n",
    "\n",
    "# 链接数据库\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base(engine)\n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "class News(Base):\n",
    "    \"\"\"信息对象\"\"\"\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_news2'\n",
    "    \n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    phone = Column(String(11), unique=True)\n",
    "    title = Column(String(32), nullable=False)\n",
    "    read_count = Column(Integer, default=1)\n",
    "    create_time = Column(DateTime, default=datetime.now)\n",
    "\n",
    "    # 当数据更新后，这个段的内容自动更新成 datetime.now\n",
    "    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)\n",
    "\n",
    "\n",
    "def create_data():\n",
    "    \"\"\"添加数据\"\"\"\n",
    "    \n",
    "    # 创建 对象\n",
    "    new1 = News(phone='16866666666', title='测试列参数')\n",
    "    \n",
    "    # 创建 Session 实例\n",
    "    with Session() as session:\n",
    "        \n",
    "        # 添加事务\n",
    "        session.add(new1)\n",
    "        \n",
    "        # 提交事务\n",
    "        session.commit()\n",
    "\n",
    "\n",
    "def update_data():\n",
    "    \"\"\"修改数据\"\"\"\n",
    "    \n",
    "    # 创建 Session 实例\n",
    "    with Session() as session:\n",
    "        \n",
    "        # 查找数据\n",
    "        new1 = session.query(News).first()\n",
    "        \n",
    "        # 修改数据\n",
    "        new1.read_count = 2\n",
    "        \n",
    "        # 提交事务\n",
    "        session.commit()\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "\n",
    "    # 映射表结构\n",
    "    Base.metadata.create_all()\n",
    "    \n",
    "    # 创建数据\n",
    "    create_data()\n",
    "    \n",
    "    # 更新数据\n",
    "    update_data()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "186a0c26",
   "metadata": {},
   "source": [
    "## query 函数"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7e115d2",
   "metadata": {},
   "source": [
    "**query 函数：** 查询数据"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98056a72",
   "metadata": {},
   "source": [
    "**传入类名：** 指定查找这个模型中所有的属性(对应查询表为全表查询)\n",
    "\n",
    "**传入类中的属性：** 可以指定只查找某个模型的其中几个属性\n",
    "\n",
    "**聚合函数:**\n",
    "- func.count：统计行的数量。\n",
    "- func.avg：求平均值。\n",
    "- func.max：求最大值。\n",
    "- func.min：求最小值。\n",
    "- func.sum：求和。\n",
    "\n",
    "**提示：**只要mysql中有的聚合函数，都可以通过func调用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1e4a4c10",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-16T12:49:24.708022Z",
     "start_time": "2023-12-16T12:49:24.549811Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "产品:1\n",
      "产品:2\n",
      "产品:3\n",
      "产品:4\n",
      "产品:5\n",
      "产品:6\n",
      "产品:7\n",
      "产品:8\n",
      "产品:9\n",
      "产品:10\n",
      "64\n",
      "83\n",
      "67\n",
      "41\n",
      "95\n",
      "37\n",
      "43\n",
      "5\n",
      "51\n",
      "68\n",
      "count： (10,)\n",
      "max： (95,)\n",
      "avg： (Decimal('55.4000'),)\n",
      "sum： (Decimal('554'),)\n"
     ]
    }
   ],
   "source": [
    "from random import randint\n",
    "\n",
    "from sqlalchemy import Column,Integer,String,func\n",
    "\n",
    "# 链接数据库\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base(engine)\n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "class Item(Base):\n",
    "    \"\"\"项目类\"\"\"\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_item'\n",
    "    \n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    title = Column(String(32))\n",
    "    price = Column(Integer)\n",
    "\n",
    "\n",
    "def create_data():\n",
    "    \"\"\"创建数据\"\"\"\n",
    "    \n",
    "    # 创建 Session 实例\n",
    "    with Session() as ses:\n",
    "        for i in range(10):\n",
    "            item = Item(title = f'产品:{i+1}',price=randint(1,100))\n",
    "            ses.add(item)\n",
    "        \n",
    "        # 提交数据\n",
    "        ses.commit()\n",
    "\n",
    "def query_model_name():\n",
    "    \"获取所有的字段\"\n",
    "    \n",
    "    # 创建 Session 实例\n",
    "    with Session() as ses:\n",
    "        \n",
    "        # 获取全部数据\n",
    "        rs = ses.query(Item).all()\n",
    "        for r in rs:\n",
    "            print(r.title)\n",
    "\n",
    "def query_model_attr():\n",
    "    \"获取指定的字段\"\n",
    "    \n",
    "    # 创建 Session 实例\n",
    "    with Session() as ses:\n",
    "        \n",
    "        # 获取指定字段\n",
    "        rs = ses.query(Item.title, Item.price).all() \n",
    "        for r in rs:\n",
    "            print(r.price)\n",
    "\n",
    "def query_by_func():\n",
    "    \"统计指定的列数据\"\n",
    "    \n",
    "    with Session() as ses:\n",
    "        rs = ses.query(func.count(Item.id)).first()\n",
    "        print(\"count：\", rs)\n",
    "        \n",
    "        rs = ses.query(func.max(Item.price)).first()\n",
    "        print(\"max：\", rs)\n",
    "        \n",
    "        rs = ses.query(func.avg(Item.price)).first()\n",
    "        print(\"avg：\", rs)\n",
    "        \n",
    "        rs = ses.query(func.sum(Item.price)).first()\n",
    "        print(\"sum：\", rs)\n",
    "        \n",
    "if __name__ =='__main__':\n",
    "    \n",
    "    # 映射表结构\n",
    "    Base.metadata.create_all()\n",
    "    \n",
    "    # 创建数据\n",
    "    create_data()\n",
    "    \n",
    "    # 获取所有字段\n",
    "    query_model_name()\n",
    "    \n",
    "    # 获取指定字段\n",
    "    query_model_attr()\n",
    "    \n",
    "    # 统计\n",
    "    query_by_func()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d9097b0a",
   "metadata": {},
   "source": [
    "## filter 过滤数据"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b4fa74a",
   "metadata": {},
   "source": [
    "过滤是数据提取的一个很重要的功能，以下对一些常用的过滤条件进行解释，并且这些过滤条件都是只能通过 filter方法 实现的：\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9258bc2c",
   "metadata": {},
   "source": [
    "**equals：**\n",
    "```python\n",
    "news = session.query(News).filter(News.title == \"title1\").first()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "396907df",
   "metadata": {},
   "source": [
    "**not equals：**\n",
    "```python\n",
    "query(User).filter(User.name != 'ed')\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82d0d011",
   "metadata": {},
   "source": [
    "**like & ilike [不区分大小写]:**\n",
    "```python\n",
    "query(User).filter(User.name.like('%ed%'))\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45ee076b",
   "metadata": {},
   "source": [
    "**in：**\n",
    "```python\n",
    "query(User).filter(User.name.in_(['ed', 'wendy', 'jack']))\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29258591",
   "metadata": {},
   "source": [
    "**not in：**\n",
    "```python\n",
    "query(User).filter(~User.name.in_(['ed', 'wendy', 'jack']))\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed7e70f4",
   "metadata": {},
   "source": [
    "**is null:**\n",
    "```python\n",
    "query(User).filter(User.name==None)\n",
    "# 或者是\n",
    "query(User).filter(User.name.is_(None))\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e5c988e",
   "metadata": {},
   "source": [
    "**is not null:**\n",
    "```python\n",
    "query(User).filter(User.name != None)\n",
    "# 或者是\n",
    "query(User).filter(User.name.isnot(None))\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4511a800",
   "metadata": {},
   "source": [
    "**and:**\n",
    "```python\n",
    "query(User).filter(and_(User.name=='ed', User.fullname=='Ed Jones'))\n",
    "# 或者是传递多个参数\n",
    "query(User).filter(User.name=='ed', User.fullname=='Ed Jones')\n",
    "# 或者是通过多次filter操作\n",
    "query(User).filter(User.name=='ed').filter(User.fullname=='Ed Jones')\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0171588",
   "metadata": {},
   "source": [
    "**or：**\n",
    "```python\n",
    "query(User).filter(or_(User.name=='ed', User.name=='wendy'))\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "076b948c",
   "metadata": {},
   "source": [
    "如果想要查看 orm底层 转换的 sql语句，可以在f ilter方法 后面不要再执行任何方法直接打印就可以看到了。比如：\n",
    "```python\n",
    "news = session.query(News).filter(or_(News.title=='abc', News.content=='abc'))\n",
    "print(news)\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1cb581dc",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-16T13:05:43.734868Z",
     "start_time": "2023-12-16T13:05:43.562260Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Article(title:title1 price:27.0 content:d6a69ea2-6a75-4216-8e9a-694eb3514570)>\n",
      "<Article(title:TITLE2 price:10.0 content:None)>\n",
      "[<Article(title:title1 price:27.0 content:d6a69ea2-6a75-4216-8e9a-694eb3514570)>, <Article(title:title3 price:29.0 content:1a03a4c7-1048-4d5f-b8e9-52b25e5a6791)>, <Article(title:TITLE4 price:38.0 content:None)>, <Article(title:title5 price:36.0 content:8ebeab65-f651-488d-8026-094e9676ef46)>, <Article(title:TITLE6 price:9.0 content:None)>, <Article(title:title7 price:35.0 content:d7ab1de4-7e3f-43de-93ed-ceba91f9485a)>, <Article(title:TITLE8 price:17.0 content:None)>, <Article(title:title9 price:99.0 content:de1b993d-72b1-4ee7-ad83-6ae72c62ee13)>, <Article(title:TITLE10 price:58.0 content:None)>]\n",
      "<Article(title:title1 price:27.0 content:d6a69ea2-6a75-4216-8e9a-694eb3514570)>\n",
      "<Article(title:TITLE2 price:10.0 content:None)>\n",
      "<Article(title:title3 price:29.0 content:1a03a4c7-1048-4d5f-b8e9-52b25e5a6791)>\n",
      "<Article(title:TITLE4 price:38.0 content:None)>\n",
      "<Article(title:title5 price:36.0 content:8ebeab65-f651-488d-8026-094e9676ef46)>\n",
      "<Article(title:TITLE6 price:9.0 content:None)>\n",
      "<Article(title:title7 price:35.0 content:d7ab1de4-7e3f-43de-93ed-ceba91f9485a)>\n",
      "<Article(title:TITLE8 price:17.0 content:None)>\n",
      "<Article(title:title9 price:99.0 content:de1b993d-72b1-4ee7-ad83-6ae72c62ee13)>\n",
      "<Article(title:TITLE10 price:58.0 content:None)>\n",
      "<Article(title:title1 price:27.0 content:d6a69ea2-6a75-4216-8e9a-694eb3514570)>\n",
      "<Article(title:title3 price:29.0 content:1a03a4c7-1048-4d5f-b8e9-52b25e5a6791)>\n",
      "<Article(title:TITLE6 price:9.0 content:None)>\n",
      "<Article(title:TITLE2 price:10.0 content:None)>\n",
      "<Article(title:TITLE4 price:38.0 content:None)>\n",
      "<Article(title:title5 price:36.0 content:8ebeab65-f651-488d-8026-094e9676ef46)>\n",
      "<Article(title:title7 price:35.0 content:d7ab1de4-7e3f-43de-93ed-ceba91f9485a)>\n",
      "<Article(title:TITLE8 price:17.0 content:None)>\n",
      "<Article(title:title9 price:99.0 content:de1b993d-72b1-4ee7-ad83-6ae72c62ee13)>\n",
      "<Article(title:TITLE10 price:58.0 content:None)>\n",
      "<Article(title:TITLE2 price:10.0 content:None)>\n",
      "<Article(title:TITLE4 price:38.0 content:None)>\n",
      "<Article(title:TITLE6 price:9.0 content:None)>\n",
      "<Article(title:TITLE8 price:17.0 content:None)>\n",
      "<Article(title:TITLE10 price:58.0 content:None)>\n",
      "<Article(title:title1 price:27.0 content:d6a69ea2-6a75-4216-8e9a-694eb3514570)>\n",
      "<Article(title:title3 price:29.0 content:1a03a4c7-1048-4d5f-b8e9-52b25e5a6791)>\n",
      "<Article(title:title5 price:36.0 content:8ebeab65-f651-488d-8026-094e9676ef46)>\n",
      "<Article(title:title7 price:35.0 content:d7ab1de4-7e3f-43de-93ed-ceba91f9485a)>\n",
      "<Article(title:title9 price:99.0 content:de1b993d-72b1-4ee7-ad83-6ae72c62ee13)>\n",
      "<Article(title:title9 price:99.0 content:de1b993d-72b1-4ee7-ad83-6ae72c62ee13)>\n",
      "<Article(title:TITLE10 price:58.0 content:None)>\n",
      "<Article(title:TITLE4 price:38.0 content:None)>\n",
      "<Article(title:title9 price:99.0 content:de1b993d-72b1-4ee7-ad83-6ae72c62ee13)>\n",
      "<Article(title:TITLE10 price:58.0 content:None)>\n"
     ]
    }
   ],
   "source": [
    "from random import randint\n",
    "from uuid import uuid4\n",
    "\n",
    "from sqlalchemy import Column,Integer,String,Float,Text,and_,or_\n",
    "\n",
    "# 链接数据库\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base(engine)\n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "class Article(Base):\n",
    "    \"\"\"文章类\"\"\"\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_article'\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    title = Column(String(50), nullable=False)\n",
    "    price = Column(Float, nullable=False)\n",
    "    content = Column(Text)\n",
    "    \n",
    "    def __repr__(self):\n",
    "        \"\"\"print时，显示的信息\"\"\"\n",
    "        return f\"<Article(title:{self.title} price:{self.price} content:{self.content})>\"\n",
    "\n",
    "def create_data():\n",
    "    \"\"\"创建数据\"\"\"\n",
    "    \n",
    "    # 创建 Session 实例对象\n",
    "    with Session() as ses:\n",
    "        \n",
    "        # 创建对象\n",
    "        for i in range(10):\n",
    "            if i%2 == 0:\n",
    "                art = Article(title = f'title{i+1}',price=randint(1,100),content = uuid4())\n",
    "            else:\n",
    "                art = Article(title = f'TITLE{i+1}',price=randint(1,100))\n",
    "            ses.add(art)\n",
    "            \n",
    "        # 提交事务\n",
    "        ses.commit()\n",
    "\n",
    "def query_data():\n",
    "    \"\"\"查询数据\"\"\"\n",
    "    \n",
    "    # 创建 Session 实例对象\n",
    "    with Session() as ses:\n",
    "        # rs = ses.query(Article).filter_by(id=1).first()\n",
    "        rs = ses.query(Article).filter(Article.id == 1).first()\n",
    "        print(rs)\n",
    "\n",
    "def query_data_equal():\n",
    "    with Session() as ses:\n",
    "        rs = ses.query(Article).filter(Article.title == 'title2').first()\n",
    "        print(rs)\n",
    "\n",
    "\n",
    "def query_data_not_equal():\n",
    "    with Session() as ses:\n",
    "        rs = ses.query(Article).filter(Article.title != 'title2').all()\n",
    "        print(rs)\n",
    "\n",
    "def query_data_like():\n",
    "    with Session() as ses:\n",
    "        # select * from t_article where title like 'title%';\n",
    "        rs = ses.query(Article).filter(Article.title.like('title%')).all()\n",
    "        for r in rs:\n",
    "            print(r)\n",
    "\n",
    "def query_data_in():\n",
    "    with Session() as ses:\n",
    "        rs = ses.query(Article).filter(Article.title.in_(['title1','title3','title6'])).all()\n",
    "        for r in rs:\n",
    "            print(r)\n",
    "\n",
    "def query_data_not_in():\n",
    "    with Session() as ses:\n",
    "        rs = ses.query(Article).filter(~ Article.title.in_(['title1','title3','title6'])).all()\n",
    "        for r in rs:\n",
    "            print(r)\n",
    "\n",
    "def query_data_null():\n",
    "    with Session() as ses:\n",
    "        rs = ses.query(Article).filter(Article.content == None).all()\n",
    "        for r in rs:\n",
    "            print(r)\n",
    "\n",
    "def query_data_not_null():\n",
    "    with Session() as ses:\n",
    "        rs = ses.query(Article).filter(Article.content != None).all()\n",
    "        for r in rs:\n",
    "            print(r)\n",
    "\n",
    "def query_data_and():\n",
    "    with Session() as ses:\n",
    "        # rs = ses.query(Article).filter(Article.title !='title4' and Article.price >8 ).all() \n",
    "        # rs = ses.query(Article).filter(Article.title !='title4', Article.price >50 ).all() \n",
    "        rs = ses.query(Article).filter(and_(Article.title !='title4', Article.price >50) ).all() \n",
    "        for r in rs:\n",
    "            print(r)\n",
    "\n",
    "def query_data_or():\n",
    "    with Session() as ses: \n",
    "        rs = ses.query(Article).filter(or_(Article.title =='title4', Article.price >50) ).all() \n",
    "        for r in rs:\n",
    "            print(r)\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    \n",
    "    # 映射表结构\n",
    "    Base.metadata.create_all()\n",
    "    \n",
    "    # 创建数据\n",
    "    create_data()\n",
    "    \n",
    "    # 查询数据\n",
    "    query_data()\n",
    "    query_data_equal()\n",
    "    query_data_not_equal()\n",
    "    query_data_like()\n",
    "    query_data_in()\n",
    "    query_data_not_in()\n",
    "    query_data_null()\n",
    "    query_data_not_null()\n",
    "    query_data_and()\n",
    "    query_data_or()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c9e5619",
   "metadata": {},
   "source": [
    "## 表关系"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a25ad8de",
   "metadata": {},
   "source": [
    "![](06_SQLAlchemy_images/5.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9318bf64",
   "metadata": {},
   "source": [
    "表之间的关系存在三种：一对一、一对多、多对多。而 SQLAlchemy中的ORM 也可以模拟这三种关系"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "088d8abf",
   "metadata": {},
   "source": [
    "**外键：**\n",
    "\n",
    "使用 SQLAlchemy 创建外键非常简单。在从表中增加一个字段，指定这个字段外键的是哪个表的哪个字段就可以了。\n",
    "\n",
    "从表 中外键的字段，必须和 主表 的主键字段类型保持一致。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27bdf64a",
   "metadata": {},
   "source": [
    "**外键约束有以下几项：**\n",
    "\n",
    "**RESTRICT：** 若 子表 中有 父表 对应的关联数据，删除父表对应数据，会阻止删除，默认项\n",
    "\n",
    "**NO ACTION：** 在 MySQL中，同 RESTRICT\n",
    "\n",
    "**CASCADE：** 级联删除\n",
    "\n",
    "**SET NULL：** 父表对应数据被删除，子表对应数据项会设置为 NULL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "7fa8b28b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-17T02:30:34.868520Z",
     "start_time": "2023-12-17T02:30:34.802072Z"
    }
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import Column,Integer,String,Text,ForeignKey\n",
    "\n",
    "# 链接数据库\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base(engine)\n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "class User(Base):\n",
    "    \"\"\"用户\"\"\"\n",
    "    __tablename__ = 't_user'\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    uname = Column(String(50), nullable=False, name='name')\n",
    "\n",
    "class News(Base):\n",
    "    \"\"\"消息\"\"\"\n",
    "    __tablename__ = 't_news3'\n",
    "    \n",
    "    id = Column(Integer,primary_key=True,autoincrement=True)\n",
    "    title = Column(String(50),nullable=False)\n",
    "    content = Column(Text,nullable=False)\n",
    "    \n",
    "    # uid = Column(Integer,ForeignKey('t_user.id'))  # 默认不让删主表数据\n",
    "    # uid = Column(Integer,ForeignKey('t_user.id', ondelete = 'RESTRICT')) # 默认的策略\n",
    "    # uid = Column(Integer,ForeignKey('t_user.id', ondelete = 'NO ACTION')) # 默认的策略\n",
    "    # uid = Column(Integer,ForeignKey('t_user.id', ondelete = 'CASCADE')) # 级联删除，发主表的数据被删除，子表的里数据也会删除\n",
    "    uid = Column(Integer, ForeignKey('t_user.id', ondelete = 'SET NULL')) # 发现主表数据被删除时，子表的数据列会清空\n",
    "\n",
    "\n",
    "\n",
    "def create_data():\n",
    "    \"\"\"创建数据\"\"\"\n",
    "    \n",
    "    user = User(uname = 'sxt') \n",
    "    news1 = News(title='python',content='flask',uid = 1)\n",
    "    news2 = News(title='MySQL',content='SQL',uid = 1)\n",
    "\n",
    "    with Session() as ses:\n",
    "        ses.add(user)\n",
    "        ses.commit()\n",
    "\n",
    "    with Session() as ses:\n",
    "        ses.add(news1)\n",
    "        ses.add(news2)\n",
    "        ses.commit()\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    # 映射表\n",
    "    Base.metadata.create_all()\n",
    "    \n",
    "    # 创建数据\n",
    "    create_data()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "49d35fcb",
   "metadata": {},
   "source": [
    "### 一对多的关系"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "704f5187",
   "metadata": {},
   "source": [
    "mysql级别 的外键，还不够爽，必须拿到一个表的外键，然后通过这个外键再去另外一张表中查找，这样太麻烦了。\n",
    "\n",
    "SQLAlchemy提供了一个 relationship ，这个类可以定义属性，以后在访问相关联的表的时候就直接可以通过属性访问的方式就可以访问得到了。\n",
    "\n",
    "另外，可以通过 backref 来指定反向访问的属性名称。newss是指有多篇新闻。他们之间的关系是一个“一对多”的关系"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6ff350f1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-17T02:48:34.067030Z",
     "start_time": "2023-12-17T02:48:34.015592Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<User: id=1 uname=sxt>\n",
      "[<News: id=1 title=Python content=flask uid=1>, <News: id=2 title=MySQL content=SQL uid=1>, <News: id=3 title=Python content=flask uid=1>, <News: id=4 title=MySQL content=SQL uid=1>]\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import Column,Integer,String,Text,ForeignKey,create_engine\n",
    "from sqlalchemy.orm  import relationship, sessionmaker\n",
    "\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "\n",
    "# 链接数据库\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base(engine)\n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "class User(Base):\n",
    "    \"\"\"用户\"\"\"\n",
    "    __tablename__ = 't_user'\n",
    "    id = Column(Integer,primary_key=True,autoincrement=True)\n",
    "    uname = Column(String(50),nullable=False,name='name')\n",
    "    \n",
    "    def __repr__(self):\n",
    "        \"\"\"打印方式\"\"\"\n",
    "        return f'<User: id={self.id} uname={self.uname}>'\n",
    "        \n",
    "# 1对多 ForeignKey的关键字要建立在 多一边\n",
    "class News(Base):\n",
    "    \"\"\"消息\"\"\"\n",
    "    __tablename__ = 't_news'\n",
    "    id = Column(Integer,primary_key=True,autoincrement=True)\n",
    "    title = Column(String(50), nullable=False)\n",
    "    content = Column(Text, nullable=False)\n",
    "    \n",
    "    # 外键\n",
    "    uid = Column(Integer, ForeignKey('t_user.id'))\n",
    "    \n",
    "    # 将主表的数据注入到这个字段\n",
    "    user = relationship('User', backref='news')  \n",
    "\n",
    "    def __repr__(self):\n",
    "        \"\"\"打印方式\"\"\"\n",
    "        return f'<News: id={self.id} title={self.title} content={self.content} uid={self.uid}>'\n",
    "\n",
    "def create_data():\n",
    "    \"\"\"创建数据\"\"\"\n",
    "    \n",
    "    user = User(uname = 'sxt') \n",
    "    news1 = News(title='Python',content='flask',uid = 1)\n",
    "    news2 = News(title='MySQL',content='SQL',uid = 1)\n",
    "\n",
    "    with Session() as ses:\n",
    "        ses.add(user)\n",
    "        ses.commit()\n",
    "\n",
    "    with Session() as ses:\n",
    "        ses.add(news1)\n",
    "        ses.add(news2)\n",
    "        ses.commit()\n",
    "\n",
    "\n",
    "def query_data1():\n",
    "    \"通地子表查询主表的数据\"\n",
    "    \n",
    "    with Session() as ses:\n",
    "        news1 = ses.query(News).first()\n",
    "        print(news1.user)\n",
    "\n",
    "\n",
    "def query_data2():\n",
    "    \"通地主表查找子表的数据\"\n",
    "    with Session() as ses:\n",
    "        user1 = ses.query(User).first()\n",
    "        print(user1.news)\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    \n",
    "    # 映射表\n",
    "    Base.metadata.create_all()\n",
    "    \n",
    "    # 创建数据\n",
    "    create_data()\n",
    "    \n",
    "    # 查询数据\n",
    "    query_data1()\n",
    "    query_data2()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6dc485fe",
   "metadata": {},
   "source": [
    "### 一对一的关系"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb83da21",
   "metadata": {},
   "source": [
    "在sqlalchemy中，如果想要将两个模型映射成一对一的关系，那么应该在父模型中，指定引用的时候，要传递一个 uselist=False 这个参数进去。\n",
    "\n",
    "就是告诉父模型，以后引用这个从模型的时候，不再是一个列表了，而是一个对象了"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "cc4a0f5e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-17T02:55:45.261173Z",
     "start_time": "2023-12-17T02:55:45.040019Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<User: id=1 name=百战 gender=女 address=北京>\n",
      "<LoginUser: id=1 uname=baizhan passwd=123>\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import Column,Integer,String,Text,ForeignKey,create_engine\n",
    "from sqlalchemy.orm  import relationship, sessionmaker, backref\n",
    "\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "# 链接数据库\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base(engine)\n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "class LoginUser(Base):\n",
    "    \"\"\"登录用户\"\"\"\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_user_login'\n",
    "    id = Column(Integer,primary_key=True,autoincrement=True)\n",
    "    uname = Column(String(32),nullable=False)\n",
    "    passwd = Column(String(32),nullable=False)\n",
    "\n",
    "    def __repr__(self):  \n",
    "        \"\"\"打印信息\"\"\"\n",
    "        return f'<LoginUser: id={self.id} uname={self.uname} passwd={self.passwd}>'\n",
    "\n",
    "# 创建1对1的关系, 创建一个字段来做别一个表的标识(外键)\n",
    "class User(Base):\n",
    "    \"\"\"用户\"\"\"\n",
    "    __tablename__ = 't_user'\n",
    "    id = Column(Integer,primary_key=True,autoincrement=True)\n",
    "    name = Column(String(32),nullable=False,name='name')\n",
    "    gender = Column(String(1))\n",
    "    address = Column(String(64))\n",
    "    \n",
    "    # 外键\n",
    "    login_id = Column(Integer, ForeignKey('t_user_login.id'))\n",
    "    \n",
    "    # uselist=False 指定 一对一关系\n",
    "    login_user = relationship('LoginUser',backref=backref('user', uselist=False))\n",
    "\n",
    "    def __repr__(self):\n",
    "        \"\"\"打印信息\"\"\"\n",
    "        return f'<User: id={self.id} name={self.name} gender={self.gender} address={self.address}>'\n",
    "\n",
    "\n",
    "def create_data():\n",
    "    \"\"\"创建数据\"\"\"\n",
    "    login = LoginUser(uname = 'baizhan', passwd = '123')\n",
    "    user = User(name='百战',gender = '女',address ='北京')\n",
    "    \n",
    "    # 建立关联关系\n",
    "    user.login_user = login\n",
    "\n",
    "    with Session() as ses:\n",
    "        ses.add(user)\n",
    "        ses.commit()\n",
    "\n",
    "def query_data():\n",
    "    \"\"\"查询数据\"\"\"\n",
    "    \n",
    "\n",
    "    with Session() as ses:\n",
    "        login = ses.query(LoginUser).first()\n",
    "        print(login.user)\n",
    "        \n",
    "    with Session() as ses:\n",
    "        user = ses.query(User).first()\n",
    "        print(user.login_user)\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    # 映射表\n",
    "    Base.metadata.create_all()\n",
    "    \n",
    "    # 创建数据\n",
    "    create_data()\n",
    "    \n",
    "    # 查询数据\n",
    "    query_data()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8409801b",
   "metadata": {},
   "source": [
    "### 多对多的关系"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ee31759",
   "metadata": {},
   "source": [
    "多对多的关系需要通过一张中间表来绑定他们之间的关系。\n",
    "\n",
    "先把两个需要做多对多的模型定义出来\n",
    "\n",
    "使用Table定义一个中间表，中间表一般就是包含两个模型的外键字段就可以了，并且让他们两个来作为一个“复合主键”\n",
    "\n",
    "在两个需要做多对多的模型中随便选择一个模型，定义一个relationship属性，来绑定三者之间的关系，\n",
    "\n",
    "在使用relationship的时候，需要传入一个secondary=中间表对象名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e7d71ed5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-17T03:02:18.417608Z",
     "start_time": "2023-12-17T03:02:18.330817Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[<Tag: id=1 name=IT技术>, <Tag: id=2 name=科技新闻>]\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import Column,Integer,String,Text,ForeignKey,create_engine, Table\n",
    "from sqlalchemy.orm  import relationship, sessionmaker, backref\n",
    "\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "# 链接数据库\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base(engine)\n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "# 第3张表，来关联2个模型的数据关系 ,要放在模型的上面\n",
    "news_tag = Table(\n",
    "    't_news_tag',\n",
    "    Base.metadata,\n",
    "    Column('news_id',Integer, ForeignKey('t_news.id') , primary_key=True),\n",
    "    Column('tag_id',Integer, ForeignKey('t_tag.id') , primary_key=True),\n",
    ")\n",
    "\n",
    "class News(Base):\n",
    "    \"\"\"消息\"\"\"\n",
    "    __tablename__ = 't_news'\n",
    "    id = Column(Integer,primary_key=True,autoincrement=True)\n",
    "    title = Column(String(32),nullable=False)\n",
    "\n",
    "    def __repr__(self):  \n",
    "        \"\"\"打印信息\"\"\"\n",
    "        return f'<News: id={self.id} title={self.title}>'\n",
    "\n",
    "class Tag(Base):\n",
    "    \"\"\"标签\"\"\"\n",
    "    __tablename__ = 't_tag'\n",
    "    id = Column(Integer,primary_key=True,autoincrement=True)\n",
    "    name = Column(String(32),nullable=False)\n",
    "\n",
    "    newss = relationship('News', backref='tags', \n",
    "                         secondary = news_tag) # 传入中间表名称\n",
    "\n",
    "    def __repr__(self): \n",
    "        \"\"\"打印信息\"\"\"\n",
    "        return f'<Tag: id={self.id} name={self.name}>'\n",
    "\n",
    "\n",
    "def create_data():\n",
    "    \"\"\"创建数据\"\"\"\n",
    "    news1 = News(title = 'Python又更新了！！！')\n",
    "    news2 = News(title = 'SQLAlchemy又强大了！！！')\n",
    "    tag1 = Tag(name = 'IT技术')\n",
    "    tag2 = Tag(name = '科技新闻')\n",
    "\n",
    "    # 增添关系\n",
    "    news1.tags.append(tag1)\n",
    "    news1.tags.append(tag2)\n",
    "    news2.tags.append(tag1)\n",
    "    news2.tags.append(tag2)\n",
    "\n",
    "    with Session() as ses:\n",
    "        ses.add(news1)\n",
    "        ses.add(news2)\n",
    "        ses.commit()\n",
    "\n",
    "\n",
    "def query_data():\n",
    "    \"\"\"查询数据\"\"\"\n",
    "    with Session() as ses:\n",
    "        news = ses.query(News).first()\n",
    "        print(news.tags)\n",
    "        \n",
    "if __name__ == '__main__':\n",
    "    \n",
    "    # 映射表\n",
    "    Base.metadata.create_all()\n",
    "    \n",
    "    # 创建数据\n",
    "    create_data()\n",
    "    \n",
    "    # 查询\n",
    "    query_data()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "239b07c3",
   "metadata": {},
   "source": [
    "## 删除数据 注意事项"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87fede01",
   "metadata": {},
   "source": [
    "ORM层面 删除数据，会无视mysql级别的外键约束\n",
    "\n",
    "直接会将对应的数据删除，然后将从表中的那个外键设置为NULL，也就是数据库的 SET NULL \n",
    "\n",
    "如果想要避免这种行为，应该将从表中的外键的 nullable=False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4edf020a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-17T03:05:55.958262Z",
     "start_time": "2023-12-17T03:05:55.828701Z"
    }
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import Column,Integer,String,Text,ForeignKey,create_engine, Table\n",
    "from sqlalchemy.orm  import relationship, sessionmaker, backref\n",
    "\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "\n",
    "class User(Base):\n",
    "    \"\"\"用户\"\"\"\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_user'\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    name = Column(String(32))\n",
    "\n",
    "\n",
    "class Article(Base):\n",
    "    \"\"\"文章\"\"\"\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_article'\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    title = Column(String(32))\n",
    "    \n",
    "    uid = Column(Integer, ForeignKey(\"t_user.id\"))\n",
    "    # uid = Column(Integer, ForeignKey(\"t_user.id\"),nullable = False)\n",
    "    \n",
    "    user = relationship('User',backref='articles')\n",
    "\n",
    "\n",
    "def create_data():\n",
    "    \"\"\"创建数据\"\"\"\n",
    "    # 删除已有的表\n",
    "    Base.metadata.drop_all() \n",
    "    \n",
    "    # 创建映射表\n",
    "    Base.metadata.create_all() \n",
    "    \n",
    "    # 初始化数据\n",
    "    user = User(name='SXT')\n",
    "    art1 = Article(title='Python', uid=1)\n",
    "    art2 = Article(title='MySQL', uid=1)\n",
    "    user.articles.append(art1)\n",
    "    user.articles.append(art2)\n",
    "\n",
    "    with Session() as ses:\n",
    "        ses.add(user)\n",
    "        ses.commit()\n",
    "        \n",
    "def delete_data():\n",
    "    \"\"\"删除数据\"\"\"\n",
    "    \n",
    "    # 默认删除主表数据时，会将子表的引用主表数据的外键设置Null\n",
    "    with Session() as ses:\n",
    "        user = ses.query(User).first()\n",
    "        ses.delete(user)\n",
    "        ses.commit()\n",
    "        \n",
    "if __name__ == '__main__':\n",
    "    create_data()\n",
    "    delete_data()\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15029112",
   "metadata": {},
   "source": [
    "## relationship方法中的 cascade"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e07f3bb4",
   "metadata": {},
   "source": [
    "在 SQLAlchemy，只要将一个数据添加到 session中，和他相关联的数据都可以一起存入到数据库中了。\n",
    "\n",
    "这些是怎么设置的呢？其实是通过 relationship 的时候，有一个关键字 参数cascade 可以设置这些属性\n",
    "\n",
    "**cascade 属性值为：**\n",
    "\n",
    "**save-update：** 默认选项。在添加一条数据的时候，会把其他和他相关联的数据都添加到数据库中。\n",
    "\n",
    "**delete：** 表示当删除某一个模型中的数据的时候，是否也删掉使用 relationship 和他关联的数据\n",
    "\n",
    "**delete-orphan：** 表示当对一个 ORM对象 解除了父表中的关联对象的时候，自己便会被删除掉。当然如果父表中的数据被删除，自己也会被删除。这个选项只能用在一对多上，并且还需要在子模型中的 relationship 中，增加一个 single_parent=True 的参数。\n",
    "\n",
    "**merge：** 默认选项。当在使用session.merge，合并一个对象的时候，会将使用了relationship相关联的对象也进行merge操作。\n",
    "\n",
    "**expunge：** 移除操作的时候，会将相关联的对象也进行移除。这个操作只是从session中移除，并不会真正的从数据库中删除。\n",
    "\n",
    "**all：** 是对save-update, merge, refresh-expire, expunge, delete几种的缩写。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5eee36ff",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-17T03:23:52.912010Z",
     "start_time": "2023-12-17T03:23:52.833564Z"
    }
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import Column,Integer,String,Text,ForeignKey,create_engine, Table\n",
    "from sqlalchemy.orm  import relationship, sessionmaker, backref\n",
    "\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "# 链接数据库\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base(engine)\n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "\n",
    "\n",
    "class User(Base):\n",
    "    \"\"\"用户\"\"\"\n",
    "    __tablename__ = 't_user'\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    name = Column(String(32))\n",
    "\n",
    "    # articles = relationship('Article',backref='user',cascade='') \n",
    "    \n",
    "    # 默认cascade的值是save-update\n",
    "    # articles = relationship('Article',backref='user',cascade='save-update') \n",
    "    \n",
    "    # delete可以帮助删除关联表的数据\n",
    "    # articles = relationship('Article',backref='user',cascade='save-update,delete') \n",
    "    \n",
    "    # 当关联关系被解除时，子表数据会被清空\n",
    "    # articles = relationship('Article',backref='user',cascade='save-update,delete,delete-orphan',single_parent=True)  \n",
    "\n",
    "class Article(Base):\n",
    "    \"\"\"文章\"\"\"\n",
    "    __tablename__ = 't_article'\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    title = Column(String(32))\n",
    "    uid = Column(Integer, ForeignKey(\"t_user.id\"))\n",
    "    \n",
    "    # user = relationship('User',backref='articles',cascade='save-update,delete') # 会把主表的数据删除\n",
    "    user = relationship('User',backref=backref('articles',cascade='save-update, delete, delete-orphan')) \n",
    "\n",
    "\n",
    "def create_data():\n",
    "    \"\"\"创建数据\"\"\"\n",
    "    \n",
    "    # 删除已有的表\n",
    "    Base.metadata.drop_all() \n",
    "    \n",
    "    # 创建表\n",
    "    Base.metadata.create_all() \n",
    "    \n",
    "    # 初始化数据\n",
    "    user = User(name='SXT')\n",
    "    art1 = Article(title='Python', uid=1)\n",
    "    art2 = Article(title='MySQL', uid=1)\n",
    "    user.articles.append(art1)\n",
    "    user.articles.append(art2)\n",
    "    \n",
    "    # 保存数据\n",
    "    with Session() as ses:\n",
    "        ses.add(user)\n",
    "        ses.commit()\n",
    "        \n",
    "def delete_data():\n",
    "    \"\"\"删除用户数据\"\"\"\n",
    "    with Session() as ses:\n",
    "        user = ses.query(User).first()\n",
    "        ses.delete(user)\n",
    "        ses.commit()\n",
    "\n",
    "def delete_art():\n",
    "    \"\"\"删除文章数据\"\"\"\n",
    "    with Session() as ses:\n",
    "        art = ses.query(Article).first()\n",
    "        ses.delete(art)\n",
    "        ses.commit()\n",
    "\n",
    "def update_data():\n",
    "    with Session() as ses:\n",
    "        user = ses.query(User).first()\n",
    "        user.articles = []\n",
    "        ses.commit()\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    create_data()\n",
    "    delete_data()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f5215cb",
   "metadata": {},
   "source": [
    "## 排序"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7bf9d10",
   "metadata": {},
   "source": [
    "**order_by方法排序：** 可以指定根据模型中某个属性进行排序， \"模型名.属性名.desc()\" 代表的是 降序排序\n",
    "\n",
    "**relationship的方法中order_by属性：** 在指定 relationship方法 的时候，添加 order_by属性 来指定排序的字段。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "939a843d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-17T03:34:36.602745Z",
     "start_time": "2023-12-17T03:34:36.509092Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<User: id=8 name=name7 age=30>\n",
      "<User: id=9 name=name8 age=26>\n",
      "<User: id=3 name=name2 age=21>\n",
      "<User: id=4 name=name3 age=21>\n",
      "<User: id=5 name=name4 age=21>\n",
      "<User: id=1 name=name0 age=20>\n",
      "<User: id=6 name=name5 age=19>\n",
      "<User: id=7 name=name6 age=10>\n",
      "<User: id=2 name=name1 age=8>\n",
      "<User: id=10 name=name9 age=6>\n",
      "=========================\n",
      "<User: id=3 title=title2 content=info2 read_count=41>\n",
      "<User: id=9 title=title8 content=info8 read_count=67>\n",
      "<User: id=10 title=title9 content=info9 read_count=343>\n",
      "<User: id=8 title=title7 content=info7 read_count=364>\n",
      "<User: id=7 title=title6 content=info6 read_count=422>\n",
      "<User: id=2 title=title1 content=info1 read_count=438>\n",
      "<User: id=1 title=title0 content=info0 read_count=655>\n",
      "<User: id=6 title=title5 content=info5 read_count=699>\n",
      "<User: id=5 title=title4 content=info4 read_count=793>\n",
      "<User: id=4 title=title3 content=info3 read_count=839>\n"
     ]
    }
   ],
   "source": [
    "from random import randint\n",
    "from sqlalchemy import Column,Integer,String,Text,ForeignKey,create_engine, Table\n",
    "from sqlalchemy.orm  import relationship, sessionmaker, backref\n",
    "\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "# 链接数据库\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base(engine)\n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "\n",
    "class User(Base):\n",
    "    \"\"\"用户\"\"\"\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_user'\n",
    "    \n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    name = Column(String(32))\n",
    "    age = Column(Integer)\n",
    "\n",
    "    def __repr__(self):\n",
    "        \"\"\"打印数据\"\"\"\n",
    "        return f'<User: id={self.id} name={self.name} age={self.age}>'   \n",
    "\n",
    "class News(Base):\n",
    "    \"\"\"消息\"\"\"\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_news'\n",
    "    \n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    title = Column(String(32), nullable=False)\n",
    "    content = Column(String(32), nullable=False)\n",
    "    read_count = Column(Integer)\n",
    "\n",
    "    # 外键\n",
    "    uid = Column(Integer, ForeignKey('t_user.id'))\n",
    "    \n",
    "    # 添加排序\n",
    "    user = relationship('User', backref=backref('newss', order_by=read_count))\n",
    "\n",
    "    def __repr__(self):\n",
    "        \"\"\"打印信息\"\"\"\n",
    "        return f'<User: id={self.id} title={self.title} content={self.content} read_count={self.read_count}>'   \n",
    "\n",
    "def create_data():\n",
    "    \"\"\"创建数据\"\"\"\n",
    "    with Session() as ses:\n",
    "        for i in range(10):\n",
    "            user = User(name = f'name{i}',age = randint(6, 30))\n",
    "            ses.add(user)\n",
    "        for i in range(10):\n",
    "            news = News(title=f'title{i}',content=f'info{i}',read_count= randint(0,1000))\n",
    "            user.newss.append(news)\n",
    "        ses.commit()\n",
    "\n",
    "def query_user():\n",
    "    \"\"\"查询用户数据\"\"\"\n",
    "    \n",
    "    with Session() as ses:\n",
    "        \n",
    "        # users = ses.query(User).order_by(User.age).all() # 升序\n",
    "        users = ses.query(User).order_by(User.age.desc()).all() # 降序\n",
    "        \n",
    "        for u in users:\n",
    "            print(u)\n",
    "\n",
    "def query_news():\n",
    "    \"\"\"查询消息，按阅读量升序排列\"\"\"\n",
    "    with Session() as ses:\n",
    "        users = ses.query(User).all()\n",
    "        news  = users[-1].newss\n",
    "        for n in news:\n",
    "            print(n)\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    # 删除已有表\n",
    "    Base.metadata.drop_all()\n",
    "    \n",
    "    # 映射表\n",
    "    Base.metadata.create_all()\n",
    "    \n",
    "    # 创建数据\n",
    "    create_data()\n",
    "    \n",
    "    # 查询数据\n",
    "    query_user()\n",
    "    print(\"=========================\")\n",
    "    query_news()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1e68555",
   "metadata": {},
   "source": [
    "## limit、offset、slice 使用"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28831144",
   "metadata": {},
   "source": [
    "![](06_SQLAlchemy_images/6.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ca16cd9",
   "metadata": {},
   "source": [
    "**limit：** 可以限制查询的时候只查询前几条数据。 属 top-N 查询\n",
    "\n",
    "**offset：** 可以限制查找数据的时候过滤掉前面多少条。可指定开始查询时的偏移量\n",
    "\n",
    "**切片：** 可以对Query对象使用切片操作，来获取想要的数据。\n",
    "- 可以使用 slice(start,stop) 方法来做切片操作。\n",
    "- 也可以使用 [start:stop] 的方式来进行切片操作。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f5292c75",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-17T03:47:06.733598Z",
     "start_time": "2023-12-17T03:47:06.646095Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<User: id=4 title=title3 content=info3 read_count=693>\n",
      "<User: id=5 title=title4 content=info4 read_count=646>\n",
      "<User: id=6 title=title5 content=info5 read_count=951>\n"
     ]
    }
   ],
   "source": [
    "from random import randint\n",
    "from sqlalchemy import Column, Integer, String, Text, ForeignKey, create_engine, Table\n",
    "from sqlalchemy.orm  import relationship, sessionmaker, backref\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "\n",
    "# 链接数据库\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base(engine)\n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "class News(Base):\n",
    "    \"\"\"消息\"\"\"\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_news'\n",
    "    id = Column(Integer,primary_key=True,autoincrement=True)\n",
    "    title = Column(String(32),nullable=False)\n",
    "    content = Column(String(32),nullable=False)\n",
    "    read_count = Column(Integer)\n",
    "\n",
    "    def __repr__(self):\n",
    "        \"\"\"打印消息\"\"\"\n",
    "        return f'<User: id={self.id} title={self.title} content={self.content} read_count={self.read_count}>'   \n",
    "\n",
    "\n",
    "def create_data():\n",
    "    \"\"\"创建数据\"\"\"\n",
    "    \n",
    "    # 删除已有表\n",
    "    Base.metadata.drop_all()\n",
    "    \n",
    "    # 映射表\n",
    "    Base.metadata.create_all()\n",
    "    \n",
    "    with Session() as ses:\n",
    "        for i in range(10):\n",
    "            news = News(title=f'title{i}',content=f'info{i}',read_count= randint(0,1000))\n",
    "            ses.add(news)\n",
    "        ses.commit()\n",
    "\n",
    "def query_by_limit():\n",
    "    \"\"\"限定查询数量\"\"\"\n",
    "    \n",
    "    with Session() as ses:\n",
    "        newss = ses.query(News).limit(3).all()\n",
    "        for n in newss:\n",
    "            print(n)\n",
    "\n",
    "def query_by_offset():\n",
    "    \"\"\"指定查询时的偏移量\"\"\"\n",
    "    with Session() as ses:\n",
    "        newss = ses.query(News).offset(3).all()\n",
    "        for n in newss:\n",
    "            print(n)         \n",
    "\n",
    "def query_by_page():\n",
    "    \"\"\"分页查询\"\"\"\n",
    "    \n",
    "    with Session() as ses:\n",
    "        # (pagenum-1)*pagesize\n",
    "        newss = ses.query(News).limit(3).offset(3).all()\n",
    "        for n in newss:\n",
    "            print(n) \n",
    "\n",
    "def query_by_slice():\n",
    "    \"\"\"切片查询\"\"\"\n",
    "    \n",
    "    with Session() as ses:\n",
    "        \n",
    "        # 从哪个索引开始,到哪个索引结束\n",
    "        newss = ses.query(News).slice(3,6).all()\n",
    "        for n in newss:\n",
    "            print(n) \n",
    "\n",
    "def query_by_slice2():\n",
    "    \"\"\"切片查询2\"\"\"\n",
    "    \n",
    "    with Session() as ses:\n",
    "        # 从哪个索引开始,到哪个索引结束\n",
    "        newss = ses.query(News).all()[3:6]\n",
    "        for n in newss:\n",
    "            print(n) \n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    create_data()\n",
    "    # query_by_limit()\n",
    "    # query_by_offset()\n",
    "    # query_by_page()\n",
    "    # query_by_slice()\n",
    "    query_by_slice2()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0550fd0",
   "metadata": {},
   "source": [
    "## 懒加载"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "caf1eec7",
   "metadata": {},
   "source": [
    "![](06_SQLAlchemy_images/7.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acca43b0",
   "metadata": {},
   "source": [
    "在一对多，或者多对多关系的时候，如果想要获取多的一方这一部分的数据的时候，往往能通过一个属性就可以全部获取了。\n",
    "\n",
    "如有一个作者，想要这个作者的所有文章，通过user.articles就可以获取所有的文章\n",
    "\n",
    "但有时候我们不想获取所有的数据，如只想获取这个作者今天发表的文章，那么这时候我们可以给relationship方法添加属性lazy='dynamic' \n",
    "\n",
    "以后通过 user.articles 获取到的就不是一个列表，而是一个AppenderQuery对象了。\n",
    "\n",
    "这样就可以对这个对象再进行一层过滤和排序等操作"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c67dd61",
   "metadata": {},
   "source": [
    "**lazy 可用的选项：**\n",
    "\n",
    "**1 select：** (默认) 后台会用select语句一次性加载所有数据，即访问到属性的时候，就会全部加载该属性的数据\n",
    "\n",
    "**2 joined：** 数据会被JOIN语句加载，即对关联的两个表进行join操作，从而获取到所有相关的对象\n",
    "\n",
    "**3 subquery：** 数据被用subquery子查询SQL语句加载\n",
    "\n",
    "**4 dynamic：** 这个也是懒加载。在访问属性的时候，并不在内存中加载数据，而是返回一个 AppenderQuery 对象, 需要执行相应方法才可以获取对象。适用于数据量大的时候\n",
    "\n",
    "注意：\n",
    "lazy=\"dynamic\" 只可以用在一对多和多对对关系中，不可以用在一对一和多对一中。\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ac0100c2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-17T04:22:42.014314Z",
     "start_time": "2023-12-17T04:22:41.926098Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT t_news.id AS t_news_id, t_news.title AS t_news_title, t_news.content AS t_news_content, t_news.read_count AS t_news_read_count, t_news.uid AS t_news_uid \n",
      "FROM t_news \n",
      "WHERE %(param_1)s = t_news.uid\n",
      "<class 'sqlalchemy.orm.dynamic.AppenderQuery'>\n"
     ]
    }
   ],
   "source": [
    "from random import randint\n",
    "from sqlalchemy import Column, Integer, String, Text, ForeignKey, create_engine, Table\n",
    "from sqlalchemy.orm  import relationship, sessionmaker, backref\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "\n",
    "# 链接数据库\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base(engine)\n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "\n",
    "class User(Base):\n",
    "    \"\"\"用户数据\"\"\"\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_user'\n",
    "    \n",
    "    id = Column(Integer, primary_key=True,autoincrement=True)\n",
    "    name = Column(String(32))\n",
    "    age = Column(Integer)\n",
    "\n",
    "    def __repr__(self):\n",
    "        \"\"\"打印数据\"\"\"\n",
    "        return f'<User: id={self.id} name={self.name} age={self.age}>'   \n",
    "\n",
    "class News(Base):\n",
    "    \"\"\"消息数据\"\"\"\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_news'\n",
    "    \n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    title = Column(String(32), nullable=False)\n",
    "    content = Column(String(32), nullable=False)\n",
    "    read_count = Column(Integer)\n",
    "\n",
    "    # 外键\n",
    "    uid = Column(Integer, ForeignKey('t_user.id'))\n",
    "    \n",
    "    # 添加关联，并运用懒加载\n",
    "    user = relationship('User', backref=backref('newss', lazy='dynamic'))\n",
    "\n",
    "    def __repr__(self):\n",
    "        \"\"\"打印数据\"\"\"\n",
    "        return f'<News: id={self.id} title={self.title} content={self.content} read_count={self.read_count}>'   \n",
    "\n",
    "def create_data():\n",
    "    \"\"\"创建数据\"\"\"\n",
    "    \n",
    "    with Session() as ses:\n",
    "        for i in range(10):\n",
    "            user = User(name = f'name{i}',age = randint(6, 30))\n",
    "            ses.add(user)\n",
    "        for i in range(10):\n",
    "            news = News(title=f'title{i}',content=f'info{i}',read_count= randint(0,1000))\n",
    "            user.newss.append(news)\n",
    "            \n",
    "        ses.commit()\n",
    "\n",
    "def query_data():\n",
    "    \"\"\"查询数据\"\"\"\n",
    "    with Session() as ses:\n",
    "        users = ses.query(User)\n",
    "        print(users)\n",
    "        print(type(users))\n",
    "\n",
    "\n",
    "def query_data2():\n",
    "    \"\"\"懒加载\"\"\"\n",
    "    with Session() as ses:\n",
    "        users = ses.query(User).all()\n",
    "        print(users[-1].newss)\n",
    "        print(type(users[-1].newss))\n",
    "\n",
    "def query_data3():\n",
    "    \"\"\"懒加载，并进行过滤\"\"\"\n",
    "    # 'lazy = select 默认不能2次过滤'\n",
    "    with Session() as ses:\n",
    "        users = ses.query(User).all()\n",
    "        newss = users[-1].newss.filter(News.read_count > 500).all()\n",
    "        print(newss)\n",
    "    \n",
    "\n",
    "if __name__ == '__main__':\n",
    "    # 删除数据\n",
    "    Base.metadata.drop_all()\n",
    "    \n",
    "    # 映射表\n",
    "    Base.metadata.create_all()\n",
    "    \n",
    "    # 创建数据\n",
    "    create_data()\n",
    "    \n",
    "    # 懒加载\n",
    "    query_data2()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbc8665e",
   "metadata": {},
   "source": [
    "## 分组group_by 和 过滤分组having"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "047e35b0",
   "metadata": {},
   "source": [
    "![](06_SQLAlchemy_images/8.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "271e97c6",
   "metadata": {},
   "source": [
    "**group_by：**\n",
    "\n",
    "根据 某个字段 进行分组。如想要根据年龄进行分组，来统计每个分组分别有多少人\n",
    "```python\n",
    "r = session.query(User.age, func.count(User.id)).group_by(User.age).all()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f925c4ce",
   "metadata": {},
   "source": [
    "**having：**\n",
    "\n",
    "having 是对分组查找结果作进一步过滤。如只想要看未成年人的人数，那么可以首先对年龄进行分组统计人数，然后再对分组进行having过滤\n",
    "```python\n",
    "r = session.query(User.age, func.count(User.id)).group_by(User.age).having(User.age < 18).all()\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "bf0e7ca3",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-17T04:59:26.981488Z",
     "start_time": "2023-12-17T04:59:26.736168Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(10, 10), (7, 4), (9, 4), (14, 2), (6, 5), (16, 4), (13, 3), (15, 3), (12, 5), (11, 5), (17, 2), (8, 1)]\n"
     ]
    }
   ],
   "source": [
    "from random import randint\n",
    "from sqlalchemy import Column, Integer, String, Text, ForeignKey, create_engine, Table, func\n",
    "from sqlalchemy.orm  import relationship, sessionmaker, backref\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "\n",
    "# 链接数据库\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base(engine)\n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "\n",
    "class User(Base):\n",
    "    \"\"\"用户数据\"\"\"\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_user'\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    name = Column(String(32))\n",
    "    age = Column(Integer)\n",
    "\n",
    "    def __repr__(self):\n",
    "        \"\"\"打印数据\"\"\"\n",
    "        return f'<User: id={self.id} name={self.name} age={self.age}>'\n",
    "\n",
    "def create_data():\n",
    "    \"\"\"创建数据\"\"\"\n",
    "    \n",
    "    # 删除原有的表\n",
    "    Base.metadata.drop_all()\n",
    "    \n",
    "    # 映射表\n",
    "    Base.metadata.create_all()\n",
    "    with Session() as ses:\n",
    "        for i in range(100):\n",
    "            user = User(name = f'name{i}',age = randint(6, 30))\n",
    "            ses.add(user)\n",
    "        ses.commit()\n",
    "\n",
    "def query_by_age():\n",
    "    \"统计 每个年龄的人数有多少\"\n",
    "    with Session() as ses:\n",
    "        rs = ses.query(User.age, func.count(User.id)).group_by(User.age).all()\n",
    "        print(rs)\n",
    "        print(type(rs))\n",
    "\n",
    "def query_by_age_gt_18():\n",
    "    \"统计 每个年龄的人数有多少, 年龄大于18\"\n",
    "    with Session() as ses:\n",
    "        rs = ses.query(User.age, func.count(User.id)).group_by(User.age).having(User.age > 18).all()\n",
    "        print(rs)\n",
    "\n",
    "\n",
    "def query_by_age_lt_18():\n",
    "    \"统计 每个年龄的人数有多少, 年龄小于18\"\n",
    "    with Session() as ses:\n",
    "        rs = ses.query(User.age, func.count(User.id)).group_by(User.age).having(User.age < 18).all()\n",
    "        print(rs)\n",
    "\n",
    "if __name__ =='__main__':\n",
    "    # 创建数据\n",
    "    create_data()\n",
    "    \n",
    "    # query_by_age()\n",
    "    # query_by_age_gt_18()\n",
    "    \n",
    "    # 查询\n",
    "    query_by_age_lt_18()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96c00e0f",
   "metadata": {},
   "source": [
    "## join 多表查询"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0324ae69",
   "metadata": {},
   "source": [
    "### mysql 外连接、内连接\n",
    "\n",
    "**表A数据：**\n",
    "\n",
    "![](06_SQLAlchemy_images/9.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "546c09d3",
   "metadata": {},
   "source": [
    "**表B数据：**\n",
    "\n",
    "![](06_SQLAlchemy_images/10.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "076910d9",
   "metadata": {},
   "source": [
    "**left join(左链接)**\n",
    "```mysql\n",
    "SELECT * FROM a\n",
    "LEFT JOIN  b \n",
    "ON a.aID = b.bID\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe5cb59c",
   "metadata": {},
   "source": [
    "left join是以A表的记录为基础的, A可以看成左表, B可以看成右表, left join是以左表为准的\n",
    "\n",
    "换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID)\n",
    "\n",
    "B表记录不足的地方均为 NULL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5bc9c91d",
   "metadata": {},
   "source": [
    "**结果：**\n",
    "\n",
    "![](06_SQLAlchemy_images/11.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1680c6b7",
   "metadata": {},
   "source": [
    "**right join(右连接)**\n",
    "```mysql\n",
    "SELECT  * FROM a\n",
    "RIGHT JOING b \n",
    "ON a.aID = b.bID\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3a7d812",
   "metadata": {},
   "source": [
    "这次是以右表(B)为基础的, A表不足的地方用 NULL填充"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2a215db",
   "metadata": {},
   "source": [
    "**结果：**\n",
    "\n",
    "![](06_SQLAlchemy_images/12.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c52f1ff6",
   "metadata": {},
   "source": [
    "**inner join(相等连接 或 内连接)**\n",
    "```mysql\n",
    "SELECT * FROM  a\n",
    "INNER JOIN  b\n",
    "ON a.aID =b.bID\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfc68e62",
   "metadata": {},
   "source": [
    "等同于以下 SQL句：\n",
    "```mysql\n",
    "SELECT *\n",
    "FROM a,b\n",
    "WHERE a.aID = b.bID\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "692c8866",
   "metadata": {},
   "source": [
    "**结果：**\n",
    "\n",
    "![](06_SQLAlchemy_images/13.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c247d0f",
   "metadata": {},
   "source": [
    "### join 的使用"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "784af3be",
   "metadata": {},
   "source": [
    "join分为left join（左外连接）和 right join（右外连接）以及 内连接（等值连接）\n",
    "\n",
    "在sqlalchemy中，使用join来完成内连接。在写join的时候，如果不写join的条件，那么默认将使用外键来作为条件连接\n",
    "\n",
    "查询出来的字段，跟join后面的东西无关，而是取决于query方法中传了什么参数。（模型名=全表；模型名.属性=表名.字段）\n",
    "\n",
    "在sqlalchemy中，使用outerjoin来完成外连接（默认是左外连接）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "492e2d52",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-17T05:18:44.197980Z",
     "start_time": "2023-12-17T05:18:44.041181Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('name0', 4), ('name1', 6)]\n"
     ]
    }
   ],
   "source": [
    "from random import randint\n",
    "from sqlalchemy import Column, Integer, String, Text, ForeignKey, create_engine, Table, func\n",
    "from sqlalchemy.orm  import relationship, sessionmaker, backref\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "# 链接数据库\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base(engine)\n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "\n",
    "class User(Base):\n",
    "    \"\"\"用户数据\"\"\"\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_user'\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    name = Column(String(32))\n",
    "    age = Column(Integer)\n",
    "\n",
    "    def __repr__(self):\n",
    "        \"\"\"打印信息\"\"\"\n",
    "        return f'<User: id={self.id} name={self.name} age={self.age}>'   \n",
    "\n",
    "class News(Base):\n",
    "    \"\"\"消息数据\"\"\"\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_news'\n",
    "    id = Column(Integer,primary_key=True,autoincrement=True)\n",
    "    title = Column(String(32), nullable=False)\n",
    "    content = Column(String(32), nullable=False)\n",
    "    read_count = Column(Integer)\n",
    "\n",
    "    uid = Column(Integer)\n",
    "\n",
    "    # uid = Column(Integer,ForeignKey('t_user.id'))\n",
    "    # user = relationship('User',backref=backref('newss'))\n",
    "\n",
    "    def __repr__(self):\n",
    "        return f'<News: id={self.id} title={self.title} content={self.content} read_count={self.read_count}>'   \n",
    "\n",
    "\n",
    "def create_data():\n",
    "    \"\"\"创建数据\"\"\"\n",
    "    \n",
    "    # 清空原表\n",
    "    Base.metadata.drop_all()\n",
    "    \n",
    "    # 映射表\n",
    "    Base.metadata.create_all()\n",
    "    with Session() as ses:\n",
    "        for i in range(2):\n",
    "            user = User(name = f'name{i}',age = randint(6, 30))\n",
    "            ses.add(user)\n",
    "        for i in range(10):\n",
    "            news = News(title=f'title{i}',content=f'info{i}',read_count= randint(0,1000),uid = randint(1,2))\n",
    "            ses.add(news)\n",
    "        ses.commit()\n",
    "\n",
    "def query_join():\n",
    "    \"找到所有的用户，新闻的数量，按照发表的新闻数量进行排序\"\n",
    "    \n",
    "    with Session() as ses:\n",
    "        # join里面放对象类名时，默认会以外键做为关联条件\n",
    "        rs = ses.query(User.name, func.count(News.id)).join(News).group_by(User.id).order_by(func.count(News.id)).all()\n",
    "        '''\n",
    "        SELECT u.name,count(n.id)\n",
    "        from t_user u\n",
    "        join t_news n\n",
    "        on u.id = n.uid\n",
    "        group by u.id\n",
    "        order by count(n.id);\n",
    "        '''\n",
    "        \n",
    "        print(rs)\n",
    "        \n",
    "def query_join_by_self():\n",
    "    \"找到所有的用户，新闻的数量，按照发表的新闻数量进行排序\"\n",
    "    '''\n",
    "    SELECT u.name,count(n.id)\n",
    "    from t_user u\n",
    "    join t_news n\n",
    "    on u.id = n.uid\n",
    "    group by u.id\n",
    "    order by count(n.id);\n",
    "    '''\n",
    "    with Session() as ses:\n",
    "        # join里面放对象类名时，默认会以外键做为关联条件\n",
    "        rs = ses.query(User.name,func.count(News.id)).join(News, News.uid == User.id).group_by(User.id).order_by(func.count(News.id)).all()\n",
    "        print(rs)\n",
    "        \n",
    "if __name__ == '__main__':\n",
    "    create_data()\n",
    "    # query_join()\n",
    "    query_join_by_self()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53216d27",
   "metadata": {},
   "source": [
    "## subquery 子查询"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0e46f8c",
   "metadata": {},
   "source": [
    "子查询 即 select语句中 还有select\n",
    "\n",
    "那么在 sqlalchemy中，要实现一个子查询，**需以下几个步骤：**\n",
    "1. 将 子查询 按照传统的方式写好查询代码，然后在 query 对象后面执行 subquery 方法，将这个查询 变成 一个子查询\n",
    "2. 在子查询中，将以后需要用到的字段通过 label 方法，取个别名\n",
    "3. 在父查询中，如果想要使用子查询的字段，那么可以通过子查询的返回值上的 c 属性拿到（c=Column）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ec7285eb",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-17T05:28:24.615090Z",
     "start_time": "2023-12-17T05:28:24.511011Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[<User: id=5 name=百战 age=5>, <User: id=11 name=百战 age=5>]\n"
     ]
    }
   ],
   "source": [
    "from random import randint, choice\n",
    "from sqlalchemy import Column,Integer,String,func,and_\n",
    "from sqlalchemy.orm  import relationship, sessionmaker, backref\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "# 链接数据库\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base(engine)\n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "class User(Base):\n",
    "    \"\"\"用户数据\"\"\"\n",
    "    \n",
    "    # 表名\n",
    "    __tablename__ = 't_user'\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    name = Column(String(32))\n",
    "    age = Column(Integer)\n",
    "\n",
    "    def __repr__(self):\n",
    "        \"\"\"打印数据\"\"\"\n",
    "        return f'<User: id={self.id} name={self.name} age={self.age}>'\n",
    "\n",
    "def create_data():\n",
    "    \"\"\"创建数据\"\"\"\n",
    "    \n",
    "    # 删除原有表\n",
    "    Base.metadata.drop_all()\n",
    "    \n",
    "    # 映射表\n",
    "    Base.metadata.create_all()\n",
    "    \n",
    "    names = ['百战','尚学堂','百战程序','北京尚学堂']\n",
    "    \n",
    "    with Session() as ses:\n",
    "        for i in range(20):\n",
    "            user = User(name= choice(names),age=randint(3, 10))\n",
    "            ses.add(user)\n",
    "        ses.commit()\n",
    "\n",
    "def query_data_old():\n",
    "    \"获取一个为 百战人。 获取和它同名同年龄的人\"\n",
    "    \n",
    "    with Session() as ses:\n",
    "        # 获取一个百战人\n",
    "        u = ses.query(User).filter(User.name == '百战').first()\n",
    "        \n",
    "        # 获取同名 同年龄的人\n",
    "        users = ses.query(User).filter(User.name == u.name, User.age == u.age).all()\n",
    "        print(users)\n",
    "\n",
    "def query_data_new():\n",
    "    \"获取一个为 百战人。 获取和它同名同年龄的人\"\n",
    "    \n",
    "    with Session() as ses:\n",
    "        \n",
    "        # 将以后需要用到的字段通过 label 方法，取个别名\n",
    "        sub_sql = ses.query(User.name.label('uname'), User.age.label('uage')).filter(User.name == '百战').limit(1).subquery()\n",
    "        \n",
    "        # 如果想要使用子查询的字段，那么可以通过子查询的返回值上的 c 属性拿到（c=Column）\n",
    "        rs = ses.query(User).filter(User.name ==sub_sql.c.uname, User.age == sub_sql.c.uage).all()\n",
    "        print(rs)\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    create_data()\n",
    "    # query_data_old()\n",
    "    query_data_new()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5bbb796",
   "metadata": {},
   "source": [
    "## aliased 别名使用"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "048892fe",
   "metadata": {},
   "source": [
    "当多表关联查询的时候，有时候同一个表要用到多次，\n",
    "\n",
    "这时候用别名就可以方便的解决命名冲突的问题了"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "1c69e9a4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-17T05:34:30.346445Z",
     "start_time": "2023-12-17T05:34:30.290285Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, '河北省', 0, '石家庄', 10)\n",
      "(1, '河北省', 0, '天津市', 11)\n",
      "(2, '广东省', 0, '深圳市', 21)\n",
      "(2, '广东省', 0, '广州市', 22)\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import Column, Integer, String\n",
    "\n",
    "from sqlalchemy.orm import relationship, sessionmaker, backref, aliased\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "\n",
    "# 链接数据库\n",
    "HOST = '127.0.0.1'\n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base(engine)\n",
    "Session = sessionmaker(engine)\n",
    "\n",
    "\n",
    "class City(Base):\n",
    "    \"\"\"城市\"\"\"\n",
    "\n",
    "    # 表名\n",
    "    __tablename__ = 't_city'\n",
    "\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    name = Column(String(32))\n",
    "    pid = Column(Integer)\n",
    "\n",
    "    def __repr__(self):\n",
    "        \"\"\"打印信息\"\"\"\n",
    "        return f'<City: id={self.id} name={self.name} pid={self.pid}>'\n",
    "\n",
    "\n",
    "def create_data():\n",
    "    \"\"\"创建数据\"\"\"\n",
    "\n",
    "    # 删除原表\n",
    "    Base.metadata.drop_all()\n",
    "\n",
    "    # 映射表\n",
    "    Base.metadata.create_all()\n",
    "\n",
    "    city1 = City(id=1, name='河北省', pid=0)\n",
    "    city2 = City(id=10, name='石家庄', pid=1)\n",
    "    city3 = City(id=11, name='天津市', pid=1)\n",
    "    city4 = City(id=2, name='广东省', pid=0)\n",
    "    city5 = City(id=21, name='深圳市', pid=2)\n",
    "    city6 = City(id=22, name='广州市', pid=2)\n",
    "\n",
    "    with Session() as ses:\n",
    "        ses.add(city1)\n",
    "        ses.add(city2)\n",
    "        ses.add(city3)\n",
    "        ses.add(city4)\n",
    "        ses.add(city5)\n",
    "        ses.add(city6)\n",
    "        ses.commit()\n",
    "\n",
    "\n",
    "def query_city():\n",
    "    \"\"\"查询数据\"\"\"\n",
    "\n",
    "    # 取别名\n",
    "    c = aliased(City)\n",
    "\n",
    "    with Session() as ses:\n",
    "        # rs = ses.query(City,c.name,c.id).join(c,City.id == c.pid).all()\n",
    "        rs = ses.query(City.id, City.name, City.pid, c.name, c.id).join(c, City.id == c.pid).all()\n",
    "        for r in rs:\n",
    "            print(r)\n",
    "\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    create_data()\n",
    "    query_city()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f68611f8",
   "metadata": {},
   "source": [
    "## Flask-SQLAlchemy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b14cffbb",
   "metadata": {},
   "source": [
    "**数据库连接：**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41592bf4",
   "metadata": {},
   "source": [
    "数据库初始化 不再是通过 create_engine\n",
    "1. 跟 sqlalchemy 一样，定义好数据库连接字符串 DB_URI\n",
    "2. 将这个定义好的数据库连接字符串DB_URI，通过 SQLALCHEMY_DATABASE_URI 这个key名配置到 app.config 中\n",
    "```python\n",
    "app.config[\"SQLALCHEMY_DATABASE_URI\"] = DB_URI\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c456b4f",
   "metadata": {},
   "source": [
    "3. 使用 flask_sqlalchemy.SQLAlchemy 这个类定义一个对象，并将 app 传入进去\n",
    "```python\n",
    "db = SQLAlchemy(app)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3f1178e",
   "metadata": {},
   "source": [
    "**创建 ORM模型类：**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4f41f7d",
   "metadata": {},
   "source": [
    "之前都是通过 Base = declarative_base() 来初始化一个基类，然后再继承\n",
    "\n",
    "在Flask-SQLAlchemy中更加简单了，还是跟使用sqlalchemy一样，定义模型。\n",
    "\n",
    "现在不再是需要使用 delarative_base 来创建一个基类，而是使用 db.Model 来作为基类\n",
    "\n",
    "1. 在模型类中， Column 、 String 、 Integer 以及 relationship 等，都不需要导入了，直接使用 db下面相应的属性名就可以了\n",
    "2. 在定义模型的时候，可以不写 \\_\\_tablename__ ，那么 flask_sqlalchemy 会默认使用当前的模型的名字转换成小写来作为表的名字，并且如果这个模型的名字用到了多个单词并且使用了驼峰命名法，那么会在多个单词之间使用下划线来进行连接"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e99969c",
   "metadata": {},
   "source": [
    "**映射表：**\n",
    "\n",
    "写完模型类后，要将模型映射到数据库的表中，使用以下代码即可\n",
    " 1. 删除数据库表： db.drop_all()\n",
    " 2. 创建数据库表： db.create_all()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f07fbf2",
   "metadata": {},
   "source": [
    "**session 的使用：**\n",
    "\n",
    "以后session也不需要使用 sessionmaker 来创建了，直接使用 db.session 就可以了，\n",
    "\n",
    "操作这个session的时候就跟之前的 sqlalchemy 的 session 是一样的"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55b31072",
   "metadata": {},
   "source": [
    "**添加数据：**\n",
    "\n",
    "这时候就可以在数据库中看到已经生成了对应表了\n",
    "\n",
    "添加数据和之前的没有区别，只是session成为了一个db的属性\n",
    "\n",
    "**单表查询：**\n",
    "\n",
    "查询数据不再是之前的 session.query方法 了，而是将query属性放在了db.Model上，\n",
    "\n",
    "所以查询就是通过“模型名.query”的方式进行查询了， query 就跟之前的 sqlalchemy 中的 query方法 是一样用的。\n",
    "\n",
    "**多表查询：**\n",
    "\n",
    "如果查找数据涉及多个模型，只能使用db.session.query(模型名).all() 这种方式"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17ca5027",
   "metadata": {},
   "source": [
    "**修改数据：**\n",
    "\n",
    "修改数据和之前的没有区别，只是session成为了一个db的属性\n",
    " \n",
    "**删除数据：**\n",
    "\n",
    " 删除数据跟添加数据和修改数据类似，只不过session是db的一个属性而已"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c4e47385",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-17T05:58:14.079304Z",
     "start_time": "2023-12-17T05:58:13.943760Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(<User id=1  name=百战>, 'Python内容')]\n"
     ]
    }
   ],
   "source": [
    "from flask import Flask\n",
    "from flask_sqlalchemy import SQLAlchemy\n",
    "\n",
    "# 创建 App 对象\n",
    "app =  Flask(__name__)\n",
    "\n",
    "# 数据库的变量\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "# 配置数据库链接\n",
    "app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI\n",
    "app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False\n",
    "\n",
    "\n",
    "# 创建 ORM模型的 基类\n",
    "db =  SQLAlchemy(app)\n",
    "\n",
    "# 创建 模型类\n",
    "class User(db.Model):\n",
    "    \"\"\"用户类\"\"\"\n",
    "    \n",
    "    # 省略表名 __tablename__ = 't_user'\n",
    "    \n",
    "    id = db.Column(db.Integer, primary_key = True, autoincrement = True)\n",
    "    name = db.Column(db.String(32))\n",
    "\n",
    "    def __repr__(self):\n",
    "        \"\"\"打印消息\"\"\"\n",
    "        return f'<User id={self.id}  name={self.name}>'\n",
    "\n",
    "class News(db.Model):\n",
    "    \"\"\"消息类\"\"\"\n",
    "    id = db.Column(db.Integer, primary_key = True, autoincrement = True)\n",
    "    content = db.Column(db.String(100))\n",
    "    \n",
    "    # 外键\n",
    "    uid = db.Column(db.Integer, db.ForeignKey('user.id'))\n",
    "\n",
    "    # 关系\n",
    "    user = db.relationship('User', backref='newss')\n",
    "\n",
    "    def __repr__(self):\n",
    "        \"\"\"打印消息\"\"\"\n",
    "        return f'<News id={self.id}  content={self.content}>'\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "def create_data():\n",
    "    \"\"\"增加数据\"\"\"\n",
    "    user = User(name = '百战')\n",
    "    news = News(content = 'Python内容')\n",
    "    user.newss.append(news)\n",
    "    db.session.add(user)\n",
    "    db.session.commit()\n",
    "\n",
    "    \n",
    "def query_data_one():\n",
    "    \"\"\"查询单表数据\"\"\"\n",
    "    users = User.query.all()\n",
    "    print(users)\n",
    "\n",
    "    \n",
    "def query_data_many():\n",
    "    \"\"\"查询多表\"\"\"\n",
    "    rs = db.session.query(User, News.content).join(News,News.uid == User.id).all()\n",
    "    print(rs)\n",
    "\n",
    "    \n",
    "def update_data():\n",
    "    \"\"\"修改数据\"\"\"\n",
    "    \n",
    "    user = User.query.first()\n",
    "    user.name = '百战程序员'\n",
    "    db.session.commit()\n",
    "    \n",
    "\n",
    "def delete_data():\n",
    "    \"\"\"删除数据\"\"\"\n",
    "    news = News.query.first()\n",
    "    db.session.delete(news)\n",
    "    db.session.commit()\n",
    "    \n",
    "\n",
    "\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    with app.app_context():\n",
    "        # 删除原表\n",
    "        db.drop_all()\n",
    "        \n",
    "        # 映射表\n",
    "        db.create_all()\n",
    "\n",
    "        # 创建数据\n",
    "        create_data()\n",
    "        # query_data_one()\n",
    "        \n",
    "        # 查询数据\n",
    "        query_data_many()\n",
    "        \n",
    "        # update_data()\n",
    "        # delete_data()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de6c4721",
   "metadata": {},
   "source": [
    "## 数据库迁移工具 alembic"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f88c2103",
   "metadata": {},
   "source": [
    "具体见：Code/alembic_演示"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4985d252",
   "metadata": {},
   "source": [
    "**alembic 使用流程：**\n",
    "\n",
    "![](06_SQLAlchemy_images/14.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f02913e1",
   "metadata": {},
   "source": [
    "**1 使用sqlalchemy创建好模型类**\n",
    "\n",
    "如创建一个 models.py模块，然后在里面定义需要的模型类:\n",
    "```python\n",
    "from sqlalchemy import create_engine,Column,Integer,String\n",
    "from sqlalchemy.orm import declarative_base\n",
    "\n",
    "# 数据库的变量\n",
    "HOST = '127.0.0.1'\n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "# 创建 链接引擎\n",
    "engine = create_engine(DB_URI)\n",
    "\n",
    "# 创建 映射表的 基类\n",
    "Base = declarative_base()\n",
    "\n",
    "class User(Base):\n",
    "    \"\"\"用户\"\"\"\n",
    "\n",
    "    # 表名\n",
    "    __tablename__ = 't_user'\n",
    "    id = Column(Integer, primary_key=True, autoincrement=True)\n",
    "    name = Column(String(32))\n",
    "    age = Column(Integer)\n",
    "    gender = Column(String(2))\n",
    "    city = Column(String(32))\n",
    "\n",
    "Base.metadata.create_all(engine)\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e153b431",
   "metadata": {},
   "source": [
    "**2 使用alembic创建一个仓库（初始化仓库)**\n",
    "\n",
    "1. cd 到当前项目目录\n",
    "\n",
    "2. 执行命令 “alembic init [仓库的名字，推荐使用alembic]”"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bfcc637a",
   "metadata": {},
   "source": [
    "**3 修改配置文件**\n",
    "\n",
    "在 alembic.ini 中，给 sqlalchemy.url 项设置数据库的连接方式\n",
    "```python\n",
    "sqlalchemy.url = driver://user:pass@localhost/dbname\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e0af93b",
   "metadata": {},
   "source": [
    "为了使用模型类更新数据库，需要在 alembic/env.py 文件中设置 target_metadata项，默认为target_metadata=None。\n",
    "\n",
    "需要将 target_metadata 的值设置为模型 Base.metadata，但是要导入 models\n",
    "\n",
    "使用 sys模块 和 os模块 把当前项目的路径导入到 path 中：\n",
    "\n",
    "**导入 models 的操作为：**\n",
    "```python\n",
    "import sys,os\n",
    "sys.path.append(os.path.dirname(os.path.dirname(__file__)))\n",
    "import models\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08778cb7",
   "metadata": {},
   "source": [
    "**设置target_metadata项操作为:**\n",
    "```python\n",
    "target_metadata = models.Base.metadata\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95ed3f2b",
   "metadata": {},
   "source": [
    "**4 自动生成迁移文件**\n",
    "\n",
    "使用 alembic revision --autogenerate -m \"提示信息\" 将当前模型中的状态生成迁移文件\n",
    "\n",
    "**5 将生成的迁移文件映射到数据库中**\n",
    "\n",
    "使用alembic upgrade head将刚刚生成的迁移文件，真正映射到数据库中。\n",
    "\n",
    "同理，如果要降级，那么使用alembic downgrade head\n",
    "\n",
    "以后如果修改了模型，重复4、5步骤"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3a416a6",
   "metadata": {},
   "source": [
    "## 常用的 alembic命令"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "13845341",
   "metadata": {},
   "source": [
    "**init：** 创建一个 alembic仓库\n",
    "\n",
    "**revision：** 创建一个新的版本文件。\n",
    "- --autogenerate：自动将当前模型的修改，生成迁移脚本。\n",
    "- -m：本次迁移做了哪些修改，用户可以指定这个参数，方便回顾。\n",
    "\n",
    "**upgrade：** 将指定版本的迁移文件映射到数据库中，会执行版本文件中的upgrade函数"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3e29bea",
   "metadata": {},
   "source": [
    "如果有多个迁移脚本没有被映射到数据库中，那么会执行多个迁移脚本。\n",
    "- [head]： 代表最新的迁移脚本的版本号。\n",
    "- downgrade： 会执行指定版本的迁移文件中的downgrade函数。\n",
    "- heads： 展示head指向的脚本文件版本号。\n",
    "- history： 列出所有的迁移版本及其信息。\n",
    "- current： 展示当前数据库中的版本号。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c5c5a6a",
   "metadata": {},
   "source": [
    "在你第一次执行upgrade的时候，就会在数据库中创建一个名叫alembic_version表，\n",
    "\n",
    "这个表只会有一条数据，记录当前数据库映射的是哪个版本的迁移文件"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e404c5a3",
   "metadata": {},
   "source": [
    "## Flask-SQLAlchemy 和 alembic 结合"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "870f8c13",
   "metadata": {},
   "source": [
    "**配置好数据库连接文件 如 config.py**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a656c12f",
   "metadata": {},
   "source": [
    "```python\n",
    "HOSTNAME = '127.0.0.1'\n",
    "PORT = '3306'\n",
    "DATABASE = 'fs_alembic_demo'\n",
    "USERNAME = 'root'\n",
    "PASSWORD = 'zxydsg123'\n",
    "\n",
    "DB_URI =\"mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8\".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)\n",
    "\n",
    "SQLALCHEMY_DATABASE_URI = DB_URI\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48f6c66a",
   "metadata": {},
   "source": [
    "**将config.py文件 结合flask项目主要运行文件 如 main.py**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "844e8a92",
   "metadata": {},
   "source": [
    "```python\n",
    "from flask import Flask\n",
    "from flask_sqlalchemy import SQLAlchemy\n",
    "import config\n",
    "\n",
    "app = Flask(__name__)\n",
    "app.config.from_object(config)\n",
    "db = SQLAlchemy(app)\n",
    "\n",
    "class User(db.Model):\n",
    "    __tablename__ = 'user'\n",
    "    id = db.Column(db.Integer,primary_key=True,autoincrement=True)\n",
    "    uname = db.Column(db.String(50), nullable=False)\n",
    "    age = db.Column(db.Integer)\n",
    "    gender = db.Column(db.String(2))\n",
    "    \n",
    "@app.route('/')\n",
    "def hello_world():\n",
    "    return 'Hello World!'\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    app.run()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4bf8304f",
   "metadata": {},
   "source": [
    "**使用 alembic 创建一个仓库(初始化仓库)**\n",
    "- cd 到当前项目目录中\n",
    "- 执行命令 “alembic init [仓库的名字，推荐使用alembic]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33a8ea02",
   "metadata": {},
   "source": [
    "**修改配置文件：**\n",
    "\n",
    "在 alembic.ini 中，给 sqlalchemy.url 项设置数据库的连接方式。方式跟 sqlalchemy的方式是一样的\n",
    "```python\n",
    "sqlalchemy.url = driver://user:pass@localhost/dbname\n",
    "```\n",
    "\n",
    "给 sqlalchemy.url 项设置数据库的连接操作为：\n",
    "```python\n",
    "sqlalchemy.url =  mysql+pymysql://root:root@localhost/fs_alembic_demo?charset=utf8\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83fa4e98",
   "metadata": {},
   "source": [
    "为了使用模型类更新数据库，需要在 alembic/env.py 文件中设置target_metadata项，默认为target_metadata=None\n",
    "\n",
    "需要将 target_metadata 的值设置为模型 Base.metadata ，但是要导入 momo\n",
    "\n",
    "使用sys模块和os模块把当前项目的路径导入到path中，导入 momo 的操作为：\n",
    "```python\n",
    "import sys, os\n",
    "sys.path.append(os.path.dirname(os.path.dirname(__file__ )))\n",
    "import main\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "542a70ec",
   "metadata": {},
   "source": [
    "设置 target_metadata项 操作为：\n",
    "```python\n",
    "target_metadata = main.db.Model.metadata\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59d57463",
   "metadata": {},
   "source": [
    "**自动生成迁移文件**\n",
    "\n",
    " 使用 alembic revision --autogenerate -m \"提示信息\" 将当前模型中的状态生成迁移文件。\n",
    " \n",
    "**将生成的迁移文件映射到数据库中**\n",
    "\n",
    " 使用alembic upgrade head将刚刚生成的迁移文件，真正映射到数据库中。\n",
    " \n",
    "同理，如果要降级，那么使用alembic downgrade head。\n",
    "\n",
    "以后如果修改了模型，重复5、6步骤"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "774d24c7",
   "metadata": {},
   "source": [
    "## Flask-Migrate"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8ef36be",
   "metadata": {},
   "source": [
    "![](06_SQLAlchemy_images/15.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec0d39e9",
   "metadata": {},
   "source": [
    "具体见：Code/flask_migrate"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2979c16",
   "metadata": {},
   "source": [
    "flask-migrate 是 flask 的一个扩展模块，主要是扩展数据库表结构的。\n",
    "\n",
    "flask-migrate 是基于Alembic进行的一个封装，并集成到Flask中，\n",
    "\n",
    "所有的迁移操作其实都是Alembic做的，他能跟踪模型的变化，并将变化映射到数据库中"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "215fa4c8",
   "metadata": {},
   "source": [
    "**模型类 app.py **\n",
    "```python\n",
    "from flask import Flask\n",
    "from flask_sqlalchemy import SQLAlchemy\n",
    "\n",
    "app =  Flask(__name__)\n",
    "\n",
    "# 数据库的变量\n",
    "HOST = '127.0.0.1' \n",
    "PORT = 3306\n",
    "DATA_BASE = 'flask_db'\n",
    "USER = 'root'\n",
    "PWD = 'zxydsg123'\n",
    "DB_URI = f'mysql+pymysql://{USER}:{PWD}@{HOST}:{PORT}/{DATA_BASE}'\n",
    "\n",
    "app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI\n",
    "app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False\n",
    "\n",
    "db =  SQLAlchemy(app)\n",
    "\n",
    "# 创建模型类\n",
    "class User(db.Model):\n",
    "    __tablename__ = 't_user'\n",
    "    id = db.Column(db.Integer,primary_key = True,autoincrement = True)\n",
    "    name = db.Column(db.String(32))\n",
    "    age = db.Column(db.Integer)\n",
    "    city = db.Column(db.String(32))\n",
    "\n",
    "    def __repr__(self):\n",
    "        return f'<User id={self.id}  name={self.name}>'\n",
    "\n",
    "from flask_migrate import Migrate\n",
    "\n",
    "Migrate(app, db)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "adde7b46",
   "metadata": {},
   "source": [
    "**创建迁移仓库**\n",
    "\n",
    "这个命令会创建migrations文件夹，所有迁移文件都放在里面\n",
    "```\n",
    "终端中：flask db init\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81aa3e4b",
   "metadata": {},
   "source": [
    "**生成脚本文件**\n",
    "```\n",
    "终端中：flask db migrate\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e7d2253",
   "metadata": {},
   "source": [
    "**更新数据库**\n",
    "```\n",
    "终端中：flask db upgrade\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7097a3b",
   "metadata": {},
   "source": [
    "**返回以前的版本**\n",
    "```\n",
    "终端中：flask db downgrade version_\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ec5fe33",
   "metadata": {},
   "source": [
    "## Flask项目结构"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b798565",
   "metadata": {},
   "source": [
    "一个良好的项目结构努力可以清晰的看出来各个模块的作用，方便扩展，易于修改"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0603ac13",
   "metadata": {},
   "source": [
    "具体见：Code/flask_project"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "007ed917",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b88035a0",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e364e571",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "288px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
